MySQL commonly used date and time functions

The first three are relatively simple and will not be explained in detail

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2016-09-08 16:12:01 |
+---------------------+
1 row in set (0.04 sec)

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2016-09-08 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 16:12:22  |
+-----------+
1 row in set (0.00 sec)

DATE_ADD() can add and subtract time

The following units are common year, month and day. For example,

mysql> SELECT DATE_ADD('2016-3-12',INTERVAL 365 DAY);
+----------------------------------------+
| DATE_ADD('2016-3-12',INTERVAL 365 DAY) |
+----------------------------------------+
| 2017-03-12                             |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2016-3-12',INTERVAL -365 DAY);
+-----------------------------------------+
| DATE_ADD('2016-3-12',INTERVAL -365 DAY) |
+-----------------------------------------+
| 2015-03-13                              |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2016-3-12',INTERVAL 1 YEAR);
+---------------------------------------+
| DATE_ADD('2016-3-12',INTERVAL 1 YEAR) |
+---------------------------------------+
| 2017-03-12                            |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD('2016-3-12',INTERVAL 3 WEEK);
+---------------------------------------+
| DATE_ADD('2016-3-12',INTERVAL 3 WEEK) |
+---------------------------------------+
| 2016-04-02                            |
+---------------------------------------+
1 row in set (0.00 sec)

DATEDIFF() can get the difference between the two times:

mysql> SELECT DATEDIFF('2013-3-12','2014-3-12');
+-----------------------------------+
| DATEDIFF('2013-3-12','2014-3-12') |
+-----------------------------------+
|                              -365 |
+-----------------------------------+
1 row in set (0.03 sec)

DATE_FORMAT( )Date formatting

##

mysql> SELECT DATE_FORMAT('2014-3-2','%m/%d/%Y');
+------------------------------------+
| DATE_FORMAT('2014-3-2','%m/%d/%Y') |
+------------------------------------+
| 03/02/2014                         |
+------------------------------------+
1 row in set (0.00 sec)

The formats that can be used are:

##%DDay of the month with English prefix%dDay of the month, value (00-31)%eDay of the month, value (0-31)%fMicroseconds%Hhour(00-23)%hhour(01-12)%IHours (01-12)%iMinutes, value (00-59)%j Day of year (001-366)%k Hour (0- 23)%l hours (1-12)%Mmonth name %mMonth, value (00-12)%pAM or PM%rTime, 12-hour (hh:mm:ss AM or PM)%S Seconds(00-59)##%uweek(00-53) Monday is the first day of the week%Vweek(01-53) Sunday is the first day of the week Day, with %X use %v Week (01-53) Monday is the first day of the week, with %x use %WName of the week%wDay of the week (0=Sunday, 6=Saturday) %X year, where Sunday is the first day of the week, 4 digits, use with %V %x year, where Monday is the first day of the week, 4 digits, and %v uses %Y year, 4 Bit %y year, 2 bits

Format	Description
%a	Abbreviated week name
%b	Abbreviated month name
%c	Month, numerical value
%s	Seconds (00-59)
%T	Time, 24-hour (hh:mm: ss)
%U	week(00-53) Sunday is the first day of the week

##There are some remaining date and time functions, which are rarely used and will not be listed here.

The above is the content of commonly used date and time functions in MySQL. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!