| title | summary | aliases | ||
|---|---|---|---|---|
Date and Time Functions |
Learn how to use the data and time functions. |
|
TiDB supports all of the date and time functions available in MySQL 5.7.
Note:
- MySQL will often accept the incorrectly formatted date and time values. For example,
'2020-01-01\n\t01:01:01'and'2020-01_01\n\t01:01'are treated as valid date and time values.- TiDB makes the best effort to match MySQL's behavior, but it might not match in all instances. It is recommended to correctly format dates, because the intended behavior for incorrectly formatted values is not documented, and is often inconsistent.
Date/Time functions:
| Name | Description |
|---|---|
ADDDATE() |
Add time values (intervals) to a date value |
ADDTIME() |
Add time |
CONVERT_TZ() |
Convert from one time zone to another |
CURDATE() |
Return the current date |
CURRENT_DATE(), CURRENT_DATE |
Synonyms for CURDATE() |
CURRENT_TIME(), CURRENT_TIME |
Synonyms for CURTIME() |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP |
Synonyms for NOW() |
CURTIME() |
Return the current time |
DATE() |
Extract the date part of a date or datetime expression |
DATE_ADD() |
Add time values (intervals) to a date value |
DATE_FORMAT() |
Format date as specified |
DATE_SUB() |
Subtract a time value (interval) from a date |
DATEDIFF() |
Subtract two dates |
DAY() |
Synonym for DAYOFMONTH() |
DAYNAME() |
Return the name of the weekday |
DAYOFMONTH() |
Return the day of the month (0-31) |
DAYOFWEEK() |
Return the weekday index of the argument |
DAYOFYEAR() |
Return the day of the year (1-366) |
EXTRACT() |
Extract part of a date |
FROM_DAYS() |
Convert a day number to a date |
FROM_UNIXTIME() |
Format Unix timestamp as a date |
GET_FORMAT() |
Return a date format string |
HOUR() |
Extract the hour |
LAST_DAY |
Return the last day of the month for the argument |
LOCALTIME(), LOCALTIME |
Synonym for NOW() |
LOCALTIMESTAMP, LOCALTIMESTAMP() |
Synonym for NOW() |
MAKEDATE() |
Create a date from the year and day of year |
MAKETIME() |
Create time from hour, minute, second |
MICROSECOND() |
Return the microseconds from argument |
MINUTE() |
Return the minute from the argument |
MONTH() |
Return the month from the date passed |
MONTHNAME() |
Return the name of the month |
NOW() |
Return the current date and time |
PERIOD_ADD() |
Add a period to a year-month |
PERIOD_DIFF() |
Return the number of months between periods |
QUARTER() |
Return the quarter from a date argument |
SEC_TO_TIME() |
Converts seconds to 'HH:MM:SS' format |
SECOND() |
Return the second (0-59) |
STR_TO_DATE() |
Convert a string to a date |
SUBDATE() |
Synonym for DATE_SUB() when invoked with three arguments |
SUBTIME() |
Subtract times |
SYSDATE() |
Return the time at which the function executes |
TIME() |
Extract the time portion of the expression passed |
TIME_FORMAT() |
Format as time |
TIME_TO_SEC() |
Return the argument converted to seconds |
TIMEDIFF() |
Subtract time |
TIMESTAMP() |
With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments |
TIMESTAMPADD() |
Add an interval to a datetime expression |
TIMESTAMPDIFF() |
Subtract an interval from a datetime expression |
TO_DAYS() |
Return the date argument converted to days |
TO_SECONDS() |
Return the date or datetime argument converted to seconds since Year 0 |
UNIX_TIMESTAMP() |
Return a Unix timestamp |
UTC_DATE() |
Return the current UTC date |
UTC_TIME() |
Return the current UTC time |
UTC_TIMESTAMP() |
Return the current UTC date and time |
WEEK() |
Return the week number |
WEEKDAY() |
Return the weekday index |
WEEKOFYEAR() |
Return the calendar week of the date (1-53) |
YEAR() |
Return the year |
YEARWEEK() |
Return the year and week |
For details, see Date and Time Functions.
The function str_to_date() is supported by TiDB, but is unable to parse all date and time values. In addition, the following date and time formatting options are not implemented:
| Format | Description |
|---|---|
| "%a" | Abbreviated weekday name (Sun..Sat) |
| "%D" | Day of the month with English suffix (0th, 1st, 2nd, 3rd) |
| "%U" | Week (00..53), where Sunday is the first day of the week; WEEK() mode 0 |
| "%u" | Week (00..53), where Monday is the first day of the week; WEEK() mode 1 |
| "%V" | Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X |
| "%v" | Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x |
| "%W" | Weekday name (Sunday..Saturday) |
| "%w" | Day of the week (0=Sunday..6=Saturday) |
| "%X" | Year for the week where Sunday is the first day of the week, numeric, four digits. |
| "%x" | Year for the week, where Monday is the first day of the week, numeric, four digits. |
See issue #30082 for more details.
The default_week_format variable affects the WEEK() function.