Date and time functions
add_months
Adds a specified number of months to a given date (DATE or DATETIME). The monthsadd function provides similar functionalities.
adddate,date_add
This function is a synonym of dateadd.
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.
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. This function returns the value of expr1 - expr2 expressed in terms of unit.
date_format
Converts a date into a string according to the specified format. Currently it supports strings with a maximum of 128 bytes. If the length of the returned value exceeds 128, NULL is returned.
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. expr1 and expr2 are valid DATE or DATETIME expressions.
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. In this standard, 1 represents Monday, and 7 represents Sunday.
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. For example, the index for Sunday is 1, for Monday is 2, for Saturday is 7.
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. The default format is yyyy-MM-dd HHss. It also supports the formats in dateformat.
hour
Returns the hour for a given date. The return value ranges from 0 to 23.
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. For example, last_day('2023-05-10', 'month') returns the last day of the month in which '2023-05-10' falls.
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. The time interval is in microseconds.
microseconds_sub
Subtracts a time interval from a date value. The time interval is in microseconds.
milliseconds_diff
Returns the time difference between the start date and end date in milliseconds.
minute
Returns the minute for a given date. The return value ranges from 0 to 59.
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. The return value ranges from 1 to 12.
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). For example, next_day('2023-04-06', 'Monday') returns the date of the next Monday that occurred after '2023-04-06'.
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). For example, previous_day('2023-04-06', 'Monday') returns the date of the previous Monday that occurred before '2023-04-06'.
quarter
Returns the quarter part of a date, in the range of 1 to 4.
quarters_add
Adds a specified number of quarters to the date, accurate to the quarter.
quarters_sub
Subtracts a specified number of quarters from the date.
second
Returns the second part for a given date. The return value ranges from 0 to 59.
seconds_add
description
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. The time interval is in seconds.
str2date
Converts a string into a DATE value according to the specified format. If the conversion fails, NULL is returned.
str_to_date
Converts a string into a DATETIME value according to the specified format. If the conversion fails, NULL is returned.
str_to_jodatime
Converts a Joda-formatted string into a DATETIME value in the specified Joda DateTime format like yyyy-MM-dd HHss.
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. The formula used for the conversion is as follows:
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 datetimeexpr2 to datetimeexpr1. datetimeexpr1 and datetimeexpr2 must be of the DATE or DATETIME type.
to_date
Converts a DATETIME value into a date.
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 'HHSS'.
utc_timestamp
Returns the current UTC date and time as a value in 'YYYY-MM-DD HHSS' 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. This function works in the same way as the WEEK function in MySQL.
week_iso
Returns the ISO standard week of the year for the specified date as an integer within the range of 1 to 53.
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
description
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. This function works in the same way as the yearweek() function in MySQL.