May 16, 2021 SQL
1. SQL date function
2. ADDDATE(date, INTERVAL expr unit), ADDDATE(expr, days)
3. ADDTIME(expr1,expr2)
4. CONVERT_TZ(dt,from_tz,to_tz)
5. CURDATE()
6. CURRENT_DATE and CURRENT_DATE()
7. CURTIME()
8. CURRENT_TIME and CURRENT_TIME()
9. CURRENT_TIMESTAMP and CURRENT_TIMESTAMP()
10. DATE(expr)
11. DATEDIFF(expr1,expr2)
12. DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
13. DATE_FORMAT(date,format)
14. DATE_SUB(date,INTERVAL expr unit)
15. DAY(date)
16. DAYNAME(date)
17. DAYOFMONTH(date)
18. DAYOFWEEK(date)
19. DAYOFYEAR(date)
20. EXTRACT(unit FROM date)
21. FROM_DAYS(N)
22. FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)
23. HOUR(time)
24. LAST_DAY(date)
25. LOCALTIME and LOCALTIME()
26. LOCALTIMESTAMP and LOCALTIMESTAMP()
27. MAKEDATE(year,dayofyear)
28. MAKETIME(hour,minute,second)
29. MICROSECOND(expr)
30. MINUTE(time)
31. MONTH(date)
32. MONTHNAME(date)
33. NOW()
34. PERIOD_ADD(P,N)
35. PERIOD_DIFF(P1,P2)
36. QUARTER(date)
37. SECOND(time)
38. SEC_TO_TIME(seconds)
39. STR_TO_DATE(str,format)
40. SUBDATE(date,INTERVAL expr unit) and SUBDATE(expr,days)
41. SUBTIME(expr1,expr2)
42. SYSDATE()
43. TIME(expr)
44. TIMEDIFF(expr1,expr2)
45. TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
46. TIMESTAMPADD(unit,interval,datetime_expr)
47. TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
48. TIME_FORMAT(time,format)
49. TIME_TO_SEC(time)
50. TO_DAYS(date)
51. UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)
52. UTC_DATE, UTC_DATE()
53. UTC_TIME, UTC_TIME()
54. WEEK(date[,mode])
55. WEEKDAY(date)
56. WEEKOFYEAR(date)
57. YEAR(date)
58. YEARWEEK(date), YEARWEEK(date,mode)
The following list is for all the important functions in SQL related to date and time. T he RDBMS you are using may support more functions. The following list is based on the MySQL related database management system.
Name | Describe |
ADDDATE() | Add the date |
ADDTIME() | Increase the time |
CONVERT_TZ() | Change the current time zone to another time zone |
CURDATE() | Returns the current date |
CURRENT_DATE(), CURRENT_DATE | THE alias of CURDATE(). |
CURRENT_TIME(), CURRENT_TIME | CurTIME() alias |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | The alias for NOW(). |
CURTIME() | Returns the current time |
DATE_ADD() | Add the two dates together |
DATE_FORMAT() | Format the date in the specified format |
DATE_SUB() | Subtract the two dates |
DATE() | Extract the date portion from the date or datetime expression |
DATEDIFF() | Subtract the two dates |
DAY() | The alias of DAYOFMONTH(). |
DAYNAME() | Returns the name of the day in use in the week |
DAYOFMONTH() | Returning a day is the day of the month (1-31) |
DAYOFWEEK() | Returning a day is the first day of the week |
DAYOFYEAR() | Returning a day is the first day of the year (1-366) |
EXTRACT | Extract a part of the date |
FROM_DAYS() | Convert the number of days to a date |
FROM_UNIXTIME() | Format a date as a UNIX timestamp |
HOUR() | Extraction hours |
LAST_DAY | Returns the last day of the month in which the parameter date is located |
LOCALTIME(), LOCALTIME | The alias for NOW(). |
LOCALTIMESTAMP, LOCALTIMESTAMP() | The alias for NOW(). |
MAKEDATE() | Create a date with the year and the number of days a day is in that year |
MAKETIME | MAKETIME() |
MICROSECOND() | Microseconds are returned by parameters |
MINUTE() | Minutes are returned by parameters |
MONTH() | Returns the month of the date parameter |
MONTHNAME() | Returns the name of the month |
NOW() | Returns the current date and time |
PERIOD_ADD() | Add a period of time between date data in the year-month format |
PERIOD_DIFF() | Returns the number of months between date data in the two-year-month format |
QUARTER() | Returns the quarter in which the date parameter is located |
SEC_TO_TIME() | Convert seconds to 'HH:MM:SS' format |
SECOND() | Number of seconds returned in parameters (0-59) |
STR_TO_DATE() | Convert the string to date data |
SUBDATE() | When called with three parameters, it DATE_SUB synonym for the word "(). |
SUBTIME() | Subtract time |
SYSDATE() | Returns the moment when the function was executed |
TIME_FORMAT() | Formatting time |
TIME_TO_SEC() | Convert time parameters to seconds |
TIME() | Returns the time portion of the argument expression |
TIMEDIFF() | Subtract two times |
TIMESTAMP() | When there is only one argument, the function returns a date or datetime expression. When there are two parameters, they are added together. |
TIMESTAMPADD() | Add a period of time to the datetime expression |
TIMESTAMPDIFF() | Subtract a period of time from the datetime expression |
TO_DAYS() | Convert date parameters to days |
UNIX_TIMESTAMP() | Returns the UNIX timestamp |
UTC_DATE() | Returns the current UTC date |
UTC_TIME() | Returns the current UTC time |
UTC_TIMESTAMP() | Returns the current UTC date and time |
WEEK() | Returns the number of weeks of the argument |
WEEKDAY() | Returns the day of the week when the date parameter is returned |
WEEKOFYEAR() | The return date parameter is the first few weeks on the calendar (1-53) |
YEAR() | Returns the year in the date parameter |
YEARWEEK() | Returns the year and week |
ADDDATE() is synonym for DATE_ADD () if the second argument at the time of the call is INTERVAL. I n the same case, SUBDATE() is DATE_SUB of the synonym for the word "(). For information on interval unit parameters, see the discussion DATE_ADD () of the system.
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1998-02-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| ADDDATE('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1998-02-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
If the second argument at the time of the call is in the form of days, MySQL adds it as an integer to expr.
mysql> SELECT ADDDATE('1998-01-02', 31);
+---------------------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1998-02-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
ADDTIME() adds expr2 to expr1 and returns the results. expr1 is a time or datetime expression, and expr2 is a time expression.
mysql> SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59.999999','1 1:1:1.000002') |
+---------------------------------------------------------+
| 1998-01-02 01:01:01.000001 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
The function converts the time zone of the value dt of the datetime type from from_dt to to_dt and returns the result. If the argument is invalid, the function returns NULL.
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','GMT','MET') |
+---------------------------------------------------------+
| 2004-01-01 13:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
+---------------------------------------------------------+
| CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00') |
+---------------------------------------------------------+
| 2004-01-01 22:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the current date in the form of 'YYYYY-MM-DD' (string) or YYYYMMDD (numeric), depending on whether the function is in a string or numerical context.
mysql> SELECT CURDATE();
+---------------------------------------------------------+
| CURDATE() |
+---------------------------------------------------------+
| 1997-12-15 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CURDATE() + 0;
+---------------------------------------------------------+
| CURDATE() + 0 |
+---------------------------------------------------------+
| 19971215 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CURRENT_DATE and CURRENT_DATE () are an alias for CURDATE().
Returns the current time in the form of 'HH:MM:SS' (string) or HHMMSS (numeric), depending on whether the function is in a string or numerical context. The function represents the return value in the current time zone.
mysql> SELECT CURTIME();
+---------------------------------------------------------+
| CURTIME() |
+---------------------------------------------------------+
| 23:50:26 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CURTIME() + 0;
+---------------------------------------------------------+
| CURTIME() + 0 |
+---------------------------------------------------------+
| 235026 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
CURRENT_TIME and CURRENT_TIME () are an alias for CURTIME().
CURRENT_TIMESTAMP and CURRENT_TIMESTAMP () are the alias for NOW().
Extract the date portion of the date expression or datetime expression.
mysql> SELECT DATE('2003-12-31 01:02:03');
+---------------------------------------------------------+
| DATE('2003-12-31 01:02:03') |
+---------------------------------------------------------+
| 2003-12-31 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
DATEDIFF() returns the difference between expr1 and expr2, expressed in days. e xpr1 and expr2 should be date or datetime expressions. Only the date portion of the parameter is involved in the calculation.
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
+---------------------------------------------------------+
| DATEDIFF('1997-12-31 23:59:59','1997-12-30') |
+---------------------------------------------------------+
| 1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
These functions perform arithmetic operations on dates. D ate is a dateTIME or DATE type value that indicates the start time. T he expr expression is the interval at which date is to be increased or subtracted. e xpr is a string that can start with '-' to represent a negative time interval. Unit is a keyword that indicates the unit of expr.
The INTERVAL keyword and unit indicator are case insensitive.
The following table lists the forms that expr should take for each unit.
The unit value | Expr should be in the format |
MICROSECOND | Microseconds |
SECOND | Seconds |
MINUTE | Minutes |
HOUR | Hours |
DAY | Days |
WEEK | Week |
MONTH | Month |
QUARTER | Quarter |
YEAR | Years |
SECOND_MICROSECOND | 'Seconds. Microseconds' |
MINUTE_MICROSECOND | 'Divided. Microseconds' |
MINUTE_SECOND | 'Minutes: Seconds' |
HOUR_MICROSECOND | 'Hours. Microseconds' |
HOUR_SECOND | 'When: Minutes: Seconds' |
HOUR_MINUTE | 'When: Minutes' |
DAY_MICROSECOND | 'Days. Microseconds' |
DAY_SECOND | 'Days: Minutes: Seconds' |
DAY_MINUTE | 'Days: Minutes' |
DAY_HOUR | 'Day when' |
YEAR_MONTH | 'Year-month' |
QUARTER and WEEK are supported since MySQL 5.0.0.
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------+
| DATE_ADD('1997-12-31 23:59:59', INTERVAL... |
+---------------------------------------------------------+
| 1998-01-01 00:01:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_ADD('1999-01-01', INTERVAL 1 HOUR);
+---------------------------------------------------------+
| DATE_ADD('1999-01-01', INTERVAL 1 HOUR) |
+---------------------------------------------------------+
| 1999-01-01 01:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
The date value is formatted based on the format string.
The following placeholders can be used in format strings, and '%' must appear before a particular format character.
placeholder | Describe |
%a | Short name of the week (Sun. Sat) |
%b | Short name of the month (Jan. Dec) |
%c | Month, expressed numerically (0.. 12) |
%D | Dates in the month with English suffix (0th, 1st, 2nd, 3rd, etc.) |
%d | Date of the month, expressed numerically (00..31) |
%e | Date of the month, expressed numerically (0..31) |
%f | Microseconds (000000. 999999) |
%H | Hours (00..23) |
%h | Hours (01..12) |
%I | Hours (01..12) |
%i | minutes, expressed numerically (00..59) |
%j | The first day of the year (001.. 366) |
%k | Hours (0.. 23) |
%l | Hours (1.. 12) |
%M | The name of the month (January. December) |
%m | Month, expressed numerically (00.. 12) |
%p | AM or PM |
%r | Time, 12-hour system (hh:mm:s followed by AM or PM) |
%S | Seconds (00.59) |
%s | Seconds (00.59) |
%T | Time, 24-hour (hh:mm:ss) |
%U | Week (00.. 53), here Sunday is the first day of the week |
%u | Week (00.. 53), here Monday is the first day of the week |
%V | Week (01.. 53), where Sunday is the first day of the week; |
%v | Week (01.. 53), where Monday is the first day of the week; |
%W | The name of the date of the week (Sunday: Saturday) |
%w | Day of the week (0 s Sunday: 6=Saturday) |
%X | The year in which the week on which Sunday is the first day is located, represented by four digits; |
%x | The year in which the week on which Monday is the first day is located, represented by four digits; |
%Y | Year, represented by four digits. |
%y | Year, represented by two digits. |
%% | % literally |
%x | x, for any situations not listed above. |
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') |
+---------------------------------------------------------+
| Saturday October 1997 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00'
-> '%H %k %I %r %T %S %w');
+---------------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00....... |
+---------------------------------------------------------+
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Similar DATE_ADD () function.
DAY() is an alias for DAYOFMONTH().
Returns the name of the date in the week.
mysql> SELECT DAYNAME('1998-02-05');
+---------------------------------------------------------+
| DAYNAME('1998-02-05') |
+---------------------------------------------------------+
| Thursday |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Return date is the first day of the month, ranging from 0 to 31.
mysql> SELECT DAYOFMONTH('1998-02-03');
+---------------------------------------------------------+
| DAYOFMONTH('1998-02-03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Return date is the day of the week (1 s Sunday, 2 s Monday,.., 7 s Saturday), where the name of the date in the week corresponds to the number in accordance with ODBC standards.
mysql> SELECT DAYOFWEEK('1998-02-03');
+---------------------------------------------------------+
|DAYOFWEEK('1998-02-03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Return date is the first day of the year, ranging from 1 to 366.
mysql> SELECT DAYOFYEAR('1998-02-03');
+---------------------------------------------------------+
| DAYOFYEAR('1998-02-03') |
+---------------------------------------------------------+
| 34 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
EXTRACT() uses the same placeholder representing units as DATE_ADD() and DATE_SUB() to extract the corresponding components of the date value, not to perform date operations.
mysql> SELECT EXTRACT(YEAR FROM '1999-07-02');
+---------------------------------------------------------+
| EXTRACT(YEAR FROM '1999-07-02') |
+---------------------------------------------------------+
| 1999 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03');
+---------------------------------------------------------+
| EXTRACT(YEAR_MONTH FROM '1999-07-02 01:02:03') |
+---------------------------------------------------------+
| 199907 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Gives the number of days N and returns the DATE value.
mysql> SELECT FROM_DAYS(729669);
+---------------------------------------------------------+
| FROM_DAYS(729669) |
+---------------------------------------------------------+
| 1997-10-07 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Special care should FROM_DAYS when working with older dates using FROM_DAYS() and this function does not apply to dates prior to the birth of the Gregory calendar (1582).
Returns the date value corresponding to the UNIX timestamp, depending on the context in which the function is located, the return worthy format is also different, the string context return format is 'YYYY-MM-DD HH:MM:SS', and the numeric context return format is YYYYMMDHMMSS. T he time zone in which the value is returned is the current time zone of the system. A UNIX timestamp is an internal time represented by a system, such UNIX_TIMESTAMP return value of a system().
If given a format, the return result is formatted according to the format string, with the same DATE_FORMAT() function.
mysql> SELECT FROM_UNIXTIME(875996580);
+---------------------------------------------------------+
| FROM_UNIXTIME(875996580) |
+---------------------------------------------------------+
| 1997-10-04 22:23:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the hour portion of the time value. F or the time of day, the return values range from 0 to 23. However, the value of the TIME type can be much larger, so the HOUR function can return a value larger than 23.
mysql> SELECT HOUR('10:05:03');
+---------------------------------------------------------+
| HOUR('10:05:03') |
+---------------------------------------------------------+
| 10 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the last day of the month in which the date or datetime value is located. If the argument is invalid, return NULL.
mysql> SELECT LAST_DAY('2003-02-05');
+---------------------------------------------------------+
| LAST_DAY('2003-02-05') |
+---------------------------------------------------------+
| 2003-02-28 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
LOCALTIME and LOCALTIME() are the alias for NOW().
LOCALTIMESTAMP and LOCALTIMESTAMP() are the alias for NOW().
The corresponding date value is returned for a given year and the number of days (a day in a year). The number of days must be greater than 0, otherwise the return value is NULL.
mysql> SELECT MAKEDATE(2001,31), MAKEDATE(2001,32);
+---------------------------------------------------------+
| MAKEDATE(2001,31), MAKEDATE(2001,32) |
+---------------------------------------------------------+
| '2001-01-31', '2001-02-01' |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the corresponding time values based on the hours, minutes, and seconds given by the parameters.
mysql> SELECT MAKETIME(12,15,30);
+---------------------------------------------------------+
| MAKETIME(12,15,30) |
+---------------------------------------------------------+
| '12:15:30' |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Depending on the time or datetime expression expr, the number of microseconds is returned, with results between 0 and 999999.
mysql> SELECT MICROSECOND('12:00:00.123456');
+---------------------------------------------------------+
| MICROSECOND('12:00:00.123456') |
+---------------------------------------------------------+
| 123456 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the minute portion of the time-type value, ranging from 0 to 59.
mysql> SELECT MINUTE('98-02-03 10:05:03');
+---------------------------------------------------------+
| MINUTE('98-02-03 10:05:03') |
+---------------------------------------------------------+
| 5 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the month in the date type value, ranging from 0 to 12.
mysql> SELECT MONTH('1998-02-03')
+---------------------------------------------------------+
| MONTH('1998-02-03') |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the full name of the month in which the date value is based.
mysql> SELECT MONTHNAME('1998-02-05');
+---------------------------------------------------------+
| MONTHNAME('1998-02-05') |
+---------------------------------------------------------+
| February |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the current date and time, and the result is in the format 'YYYYY-MM-DD HH:MM:MM:SS' or YYYYMMDDHMMSS, which is returned if the function context is character-type, otherwise the latter is returned if the function is in a numerical context. The time zone in which the value is returned is the current time zone of the system.
mysql> SELECT NOW();
+---------------------------------------------------------+
| NOW() |
+---------------------------------------------------------+
| 1997-12-15 23:50:26 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Add N months to time P (in YYMM or YYYYMM) and the result format is YYYMM. Note that the time parameter P is not a date-type value.
mysql> SELECT PERIOD_ADD(9801,2);
+---------------------------------------------------------+
| PERIOD_ADD(9801,2) |
+---------------------------------------------------------+
| 199803 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the month that differs between time P1 and P2. P 1 and P2 should be either YYMM or YYYMM. Note that I, P1, and P2 are not date values.
mysql> SELECT PERIOD_DIFF(9802,199703);
+---------------------------------------------------------+
| PERIOD_DIFF(9802,199703) |
+---------------------------------------------------------+
| 11 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the quarter value where the date value date is located, ranging from 1 to 4.
mysql> SELECT QUARTER('98-04-01');
+---------------------------------------------------------+
| QUARTER('98-04-01') |
+---------------------------------------------------------+
| 2 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the second portion of the time-type value, ranging from 0 to 59.
mysql> SELECT SECOND('10:05:03');
+---------------------------------------------------------+
| SECOND('10:05:03') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Convert the seconds in the argument to the format 'HH:MM:SS' or HHMMSS, which is returned if the function is in a string type, or the latter if the context is numerical.
This DATE_FORMATE inverse function of the 20th () function, whose arguments are the string str, which represents the time and date, and a formatting string format. If the format string has both a date and a time, STR_TO_DATE() returns the value of the DATETIME() type, otherwise the value of the date type (DATE) or the time type (TIME) is returned.
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
+---------------------------------------------------------+
| STR_TO_DATE('04/31/2004', '%m/%d/%Y') |
+---------------------------------------------------------+
| 2004-04-31 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SubDATE() is the alias of DATE_SUB () when the second argument is INTERVAL. For information on units in the INTERVAL parameters, see the DATE_ADD () of the system.
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| DATE_SUB('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1997-12-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY);
+---------------------------------------------------------+
| SUBDATE('1998-01-02', INTERVAL 31 DAY) |
+---------------------------------------------------------+
| 1997-12-02 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
SUBTIME() returns expr1-expr2 in the same format as expr1. expr1 is a time or datetime expression, and expr2 is a time value.
Returns the current date and time in the format 'YYYYY-MM-DD HH:MM:MM:SS' or YYYYMMDDHMMSS, which is returned if the function is in a contextual environment as a string, or the latter if the context is numeric.
mysql> SELECT SYSDATE();
+---------------------------------------------------------+
| SYSDATE() |
+---------------------------------------------------------+
| 2006-04-12 13:47:44 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Extract the time portion of the time type or datetime expression expr and return the result as a string.
mysql> SELECT TIME('2003-12-31 01:02:03');
+---------------------------------------------------------+
| TIME('2003-12-31 01:02:03') |
+---------------------------------------------------------+
| 01:02:03 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
TIMEDIFF() returns expr1-expr2, resulting in a time-based value. expr1 and expr2 can be time-type or datetime-type expressions, but they must be of the same type.
mysql> SELECT TIMEDIFF('1997-12-31 23:59:59.000001',
-> '1997-12-30 01:01:01.000002');
+---------------------------------------------------------+
| TIMEDIFF('1997-12-31 23:59:59.000001'..... |
+---------------------------------------------------------+
| 46:58:57.999999 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
When there is only one argument, the function returns a datetime value from a date type or datetime expression. When there are two parameters, the function adds expr2 to the date or datetime value expr1 and returns the results of the datetime type.
mysql> SELECT TIMESTAMP('2003-12-31');
+---------------------------------------------------------+
| TIMESTAMP('2003-12-31') |
+---------------------------------------------------------+
| 2003-12-31 00:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Add the expression interval of the integer type to the date type or datetime expression datetime_expr expression. The unit is given by the unit parameter and should be valued as one of the following: FRAC_SECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.
Unit can add a SQL TSI prefix, such as DAY and SQL_TSI_DAY are legal.
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
+---------------------------------------------------------+
| TIMESTAMPADD(MINUTE,1,'2003-01-02') |
+---------------------------------------------------------+
| 2003-01-02 00:01:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns a date-type or datetime-type expression datetime_expr1 difference between datetime_expr2 and the date type. The unit of the result is given by the unit parameter, and the value of unit is specified as the same as the TIMESTAMPADD() function.
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
+---------------------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01') |
+---------------------------------------------------------+
| 3 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
The function is similar to the DATE_FORMAT() function, but can only have those placeholders in the format string format that are related to hours, minutes, and seconds.
If the hour portion of the time-type value is greater than 23, the %H and %k format placeholders will produce a value greater than the usual 0-23, and the other hour-related placeholders will return the hour value divided by the remaining number after 12 (modulo 12).
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
+---------------------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l') |
+---------------------------------------------------------+
| 100 100 04 04 4 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Convert time values to seconds.
mysql> SELECT TIME_TO_SEC('22:23:00');
+---------------------------------------------------------+
| TIME_TO_SEC('22:23:00') |
+---------------------------------------------------------+
| 80580 |
+---------------------------------------------------------+
1 row in set (0.00 sec)、
Given the date value date, returns the number of days (days since 0 A.D.).
mysql> SELECT TO_DAYS(950501);
+---------------------------------------------------------+
| TO_DAYS(950501) |
+---------------------------------------------------------+
| 728779 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Without any parameters, the function returns a UNIX timestamp of the unsigned integer type (seconds since '1970-01 00:00:00' UTC). I f there is a parameter date, the function returns the number of seconds from '1970-01-01 00:00:00' UTC to date. Date can be a date-type string, a DATETIME-type string, a timestamp, or a number in YYMMDD or YYYYMMDD format.
mysql> SELECT UNIX_TIMESTAMP();
+---------------------------------------------------------+
| UNIX_TIMESTAMP() |
+---------------------------------------------------------+
| 882226357 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
+---------------------------------------------------------+
| UNIX_TIMESTAMP('1997-10-04 22:23:00') |
+---------------------------------------------------------+
| 875996580 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the current UTC date in the format 'YYYYY-MM-DD' or YYYYMMDD, which is returned if the function is in a context of strings, or the latter if the context is numerical.
mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
+---------------------------------------------------------+
| UTC_DATE(), UTC_DATE() + 0 |
+---------------------------------------------------------+
| 2003-08-14, 20030814 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the current UTC time in the format 'HH:MM:SS' or HHMMSS, which is returned if the function is in a context of strings, or the latter if the context is numerical.
mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
+---------------------------------------------------------+
| UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0 |
+---------------------------------------------------------+
| 2003-08-14 18:08:04, 20030814180804 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
The function returns that the week in which date is located is the first week of the year. T he WEEK() function of the two parameters allows you to indicate whether the week starts on Sunday or Monday, and whether the return value should range from 0 to 53 or 1 to 53. If the mode parameter is ignored, the system variable default_week_format is used.
Mode | The first day of the week | Range | Week 1 is the first week |
0 | Sunday | 0-53 | The year includes a Sunday |
1 | Monday | 0-53 | The year includes more than 3 days |
2 | Sunday | 1-53 | The year includes a Sunday |
3 | Monday | 1-53 | The year includes more than 3 days |
4 | Sunday | 0-53 | The year includes more than 3 days |
5 | Monday | 0-53 | The year includes a Monday |
6 | Sunday | 1-53 | The year includes more than 3 days |
7 | Monday | 1-53 | The year includes a Monday |
mysql> SELECT WEEK('1998-02-20');
+---------------------------------------------------------+
| WEEK('1998-02-20') |
+---------------------------------------------------------+
| 7 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Return date is the day of the week (0 s Monday, 1 s Tuesday, .6 s Sunday).
mysql> SELECT WEEKDAY('1998-02-03 22:23:00');
+---------------------------------------------------------+
| WEEKDAY('1998-02-03 22:23:00') |
+---------------------------------------------------------+
| 1 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
The week in which date is returned is the first few weeks of the year, ranging from 1 to 53. WEEKOFYEAR() is a compatibility function that functions the same as WEEK (date, 3).
mysql> SELECT WEEKOFYEAR('1998-02-20');
+---------------------------------------------------------+
| WEEKOFYEAR('1998-02-20') |
+---------------------------------------------------------+
| 8 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the year portion of date, ranging from 1000 to 9999, and 0 for date 0.
mysql> SELECT YEAR('98-02-03');
+---------------------------------------------------------+
| YEAR('98-02-03') |
+---------------------------------------------------------+
| 1998 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Returns the year and number of weeks in which date is located. T he mode parameter has exactly the same meaning as the WEEK() function. For the first and last week of the year, the year in the result may be different from the year in the date parameter.
mysql> SELECT YEARWEEK('1987-01-01');
+---------------------------------------------------------+
| YEAR('98-02-03')YEARWEEK('1987-01-01') |
+---------------------------------------------------------+
| 198653 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
Note that the number of weeks here is different from the return of WEEK() because the return value of the WEEK() function is derived in the context of a given year.