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.

📄️ 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.