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.