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.