DateTime Functions
DateTime related functions are as follows:
| Name | Description |
|---|---|
| ADDDATE() | Add a time interval to a datetime |
| ADDTIME() | Add a time value to a datetime |
| CURDATE() | Return the current date |
| DATEDIFF() | Difference between the two datetimes |
| DATE_FORMAT() | Convert datetime as specified format into CHAR string |
| DATE_TRUNC() | Truncate a datetime to specified datepart |
| DATE() | Return the date part of a datetime |
| DAY() | Synonym for DAYOFMONTH() |
| DAYOFMONTH() | Return the day of the month of a datetime |
| DAYOFWEEK() | Return the weekday index of a datetime (1 = Sunday, 2 = Monday, …, 7 = Saturday) |
| FROM_DAYS() | Return a date from a day numer |
| HOUR() | Return the hour part of a datetime |
| ISOWEEK() | Iso week number of a date-time, e.g. 2017W2 |
| MINUTE() | Minute of a date-time |
| MONTH() | Month of a date-time |
| NOW() | Current date-time |
| QUARTER() | 1-4, quarter of a date-time |
| SECOND() | Second of a date-time |
| STR_TO_DATE() | Convert string to DATETIME64 |
| STR_TO_DATE32() | Convert string to DATE32 |
| STR_TO_DATETIME32() | Convert string to DATETIME32 |
| STR_TO_DATETIME64() | Convert string to DATETIME64 |
| SUBTIME() | Subtract a time interval to a date-time |
| TIME() | Return the time part from a datetime |
| TIME_BUCKET() | Convert a date-time to a bucket's starting TIMESTAMP |
| TIME_SEG() | Convert a date-time to a time segment's starting date-time |
| TIMEDIFF() | Difference (signed integer) between two date-times |
| TIMESTAMPDIFF() | Difference (signed integer) between two date-times |
| TIMESTAMP() | TIMESTAMP of a date-time |
| TIME_TO_SEC() | Convert a time value to seconds |
| TO_DAYS() | Return the number of days since year 0 (date "0000-00-00") of a datetime |
| WEEK() | Week number of a date-time |
| WEEKDAY() | 0 (Sunday) - 6 (Saturday), week day of a day-time |
| YEAR() | Year of a date-time |
| YEARDAY() | 0-365, day number of the year of a date-time |
| YEARWEEK() | Week number of a date-time, e.g. 2017W1 |
ADDDATE
adddate(date-time, time-interval)
The ADDDATE function returns a datetime by adding time-interval to date-time.
The time-interval can be either an integer representing days, a time value, a time window specifier, or an INTERVAL value.
An INTERVAL is specified as
INTERVAL expr unitThe expr determines the interval value and unit can be SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR representing the time unit.
ADDTIME
addtime(date-time, time)
The ADDTIME function returns a datetime by adding time to date-time.
The time parameter can be either an integer representing seconds, a time window specifier, a time expression, or an INTERVAL value.
DATEDIFF
datediff(date1, date2)
The DATEDIFF function returns the difference in days between (date1 - date2).
datediff(datepart, date1, date2)
This function works the same as timestampdiff() function. Note that it returns the difference specified by the datepart between (date2 - date1).
DATE_FORMAT
date_format(date-time, format)
The DATE_FORMAT function converts a date-time into a CHAR string based on the specified format.
The following format specifiers may be used in the format string.
| specifier | Description |
|---|---|
| %a | Abbreviated weekday name (Sun..Sat) |
| %A | Full weekday name (Sunday..Saturday) |
| %b | Abbreviated month name (Jan..Dec) |
| %B | Full month name (January..December) |
| %C | Year divided by 100 (00-99) |
| %d | Day of the month (00-31) |
| %D | MM/DD/YY date (08/23/01) |
| %e | Day of the month (0-31) |
| %F | YYYY-MM-DD date (2016-08-23) |
| %H | Hour in 24h format (00-23) |
| %i | Minute (00-59) |
| %I | Hour in 12h format (01-12) |
| %k | Hour in 24h format (00-23) |
| %l | Hour in 12h format (01-12) |
| %m | Month (00-12) |
| %M | Minute (00-59) |
| %p | AM or PM |
| %s | Second (00-59) |
| %S | Seconds (00-59) |
| %T | HH:MM:SS (14:55:02) |
| %Y | Year (2017) |
| %% | A % character |
DATE_TRUNC
date_trunc(date-time, datepart)
The DATE_TRUNC function truncates a date-time to specified datepart.
STR_TO_DATE
str_to_date(string, [date_time_fmt])
str_to_date32(string, [date_time_fmt])
str_to_datetime32(string, [date_time_fmt])
str_to_datetime64(string, [date_time_fmt])
The STR_TO_DATE function and related functions convert a string into a corresponding date time based on the format specified by date_time_fmt. If date_time_fmt is omitted, the current DATE_FORMAT or DATE_TIME_FORMAT configuration setting is used.
SUBTIME
subtime(date-time, time-interval)
The SUBTIME function returns a new date-time after subtracting time-interval from date-time.
The time-interval can be either an integer representing seconds or a time window specifier.
TIME_BUCKET
time_bucket(date-time, time_window)
The TIME_BUCKET function converts all date-times within the same time window to the starting TIMESTAMP of the time window. The length of a time window is indicated by time window specifiers.
TIME_SEG
time_seg(date-time, time_window, [reference_time])
The TIME_SEG function converts all date-times within the same time window to the starting date-time of the time window. The length of a time window is indicated by time window specifiers. In addition, the starting time for each time window is determined by the reference_time as a starting time. If reference_time is not given, "1970/01/01 00:00:00" is used as the default reference time.
TIMESTAMPDIFF
timestampdiff(datepart, date1, date2)
The TIMESTAMDIFF function returns the difference specified by the datepart between (date2 - date1).
The following lists available datepart types:
| datepart |
|---|
| year |
| quarter |
| month |
| week |
| day |
| hour |
| minute |
| second |