Date and time functions
add_months
Adds a specified number of months to a given date (DATE or DATETIME).
adddate,date_add
This function is a synonym of [date_add](./date_add.md).
convert_tz
Converts a DATE or DATETIME value from one time zone to another.
curdate,current_date
Obtains the current date and returns a value of the DATE type.
current_timestamp
Obtains the current date and returns a value if the DATETIME type.
current_timezone
Obtains the current time zone and returns a value of the VARCHAR type.
curtime,current_time
Obtains the current time and returns a value of the TIME type.
date
Extracts the date part of a date or datetime expression.
date_add,adddate
Adds a specified time interval to a date.
date_diff
Returns the difference between two date values in the specified unit.
date_format
Converts a date into a string according to the specified format.
date_slice
Converts a given time into the beginning or end of a time interval based on the specified time granularity.
date_sub,subdate
Subtracts the specified time interval from a date.
date_trunc
Truncates a time value based on the specified date part, such as year, day, hour, or minute.
datediff
Calculates the difference between two date values (`expr1 - expr2`) and returns a result in days.
day
Extracts the day part of a date or datetime expression and returns a value that ranges from 1 to 31.
dayofweek_iso
Returns the ISO standard day of the week for the specified date as an integer within the range of `1` to `7`.
dayname
Returns the day corresponding to a date.
dayofmonth
Obtains the day part in a date and returns a value that ranges from 1 to 31.
dayofweek
Returns the weekday index for a given date.
dayofyear
Returns the day of the year for a given date.
days_add
Adds a specified number of days to a given date or date time.
days_diff
Returns the day difference between two date expressions (`expr1` โ `expr2`), accurate to the day.
days_sub
Subtracts specified days from a date or datetime to get a new datetime.
from_days
Returns a date from 0000-01-01.
from_unixtime
Converts a UNIX timestamp into the required time format.
hour
Returns the hour for a given date.
hours_add
Adds hours to a date or datetime.
hours_diff
Returns the hour difference between two date expressions (`expr1` โ `expr2`), accurate to the hour.
hours_sub
Reduces the specified date and time by a specified number of hours.
jodatime_format
Converts the specified date into a string in the specified Joda DateTimeFormat pattern format.
last_day
Returns the last day of an input DATE or DATETIME expression based on the specified date part.
makedate
Creates and returns a date based on the given year and day of year values.
microseconds_add
Adds a time interval to a date value.
microseconds_sub
Subtracts a time interval from a date value.
milliseconds_diff
Returns the time difference between the start date and end date in milliseconds.
minute
Returns the minute for a given date.
minutes_add
Adds the specified minutes to the date, accurate to the minute.
minutes_diff
Returns the minute difference between two date expressions (`expr1` โ `expr2`), accurate to the minute.
minutes_sub
Subtracts specified minutes from the date, accurate to the minute.
month
Returns the month for a given date.
monthname
Returns the name of the month for a given date.
months_add
Adds a specified number of months to the date, accurate to the month.
months_diff
Returns the month difference between two date expressions (`expr1` โ `expr2`), accurate to the month.
months_sub
Subtracts specified months from the date.
next_day
Returns the date of the first specified day of week (DOW) that occurs after the input date (DATE or DATETIME).
now, current_timestamp, localtime, localtimestamp
Returns the current date and time.
previous_day
Returns the date of the first specified day of week (DOW) that occurs before the input date (DATE or DATETIME).
quarter
Returns the quarter part of a date, in the range of 1 to 4.
sec_to_time
The SEC_TO_TIME function converts a value in seconds into a TIME type, returning the result in the format HH:MM:SS.
second
Returns the second part for a given date.
seconds_add
DATETIME SECONDS_ADD(DATETIME expr1,INT expr2)
seconds_diff
Returns the second difference between two date expressions (`expr1` โ `expr2`), accurate to the second.
seconds_sub
Subtracts a time interval from a date value.
str2date
Converts a string into a DATE value according to the specified format.
str_to_date
Converts a string into a DATETIME value according to the specified format.
str_to_jodatime
Converts a Joda-formatted string into a DATETIME value in the specified Joda DateTime format like `yyyy-MM-dd HH:mm:ss`.
time_format
Formats TIME-type values in the specified format.
time_slice
Converts a given time into the beginning or end of a time interval based on the specified time granularity.
time_to_sec
Converts a time value into the number of seconds.
timediff
Returns the difference between two DATETIME expressions.
timestamp
Returns the DATETIME value of a date or datetime expression.
timestampadd
Adds an integer expression interval to the date or datetime expression `datetime_expr`.
timestampdiff
Returns the interval from `datetime_expr2` to `datetime_expr1`.
to_date
Converts a DATETIME value into a date.
to_datetime
Converts a Unix timestamp to a DATETIME type value **based on the current time zone settings**.
to_datetime_ntz
Converts a Unix timestamp to a DATETIME type value.
to_days
Returns the number of days between a date and 0000-01-01.
to_iso8601
Converts the specified date into a string in ISO 8601 format.
to_tera_date
Parses a date or time string according to the specified format and converts the string to a DATE value.
to_tera_timestamp
Parses a date or time string according to the specified format and converts the string to a DATETIME value.
unix_timestamp
Converts a DATE or DATETIME value into a UNIX timestamp.
utc_time
Returns the current UTC time as a value in 'HH:MM:SS'.
utc_timestamp
Returns the current UTC date and time as a value in 'YYYY-MM-DD HH:MM:SS' or 'YYYYMMDDHHMMSS' format depending on the usage of the function, for example, in a string or numeric context.
week
Returns the week number for a given date.
week_iso
Returns the ISO standard week of the year for the specified date as an integer within the range of `1` to `53`.
weekday
Returns the weekday index for a given date.
weekofyear
Returns the week number for a given date within a year.
weeks_add
Returns the value with the number of weeks added to date.
weeks_diff
Returns the week difference between two date expressions (*`expr1`* โ *`expr2`*), accurate to the week.
weeks_sub
Subtracts a specified number of weeks from a datetime or date value.
year
Returns the year part in a date and returns a value that ranges from 1000 to 9999.
years_add
DATETIME YEARS_ADD(DATETIME expr1,INT expr2)
years_diff
Returns the year difference between two date expressions (`expr1` โ `expr2`), accurate to the year.
years_sub
Subtracts the specified number of years from the specified datetime or date.
yearweek
Returns year and week number for a given date.