Mysql的日期和时间函数-蒲公英云

一使用日期函数获取系统当前日期

mysql> SELECT CURDATE(),CURRENT_DATE(), CURDATE() + 0;
+------------+----------------+---------------+
| CURDATE()  | CURRENT_DATE() | CURDATE() + 0 |
+------------+----------------+---------------+
| 2020-01-05 | 2020-01-05     |      20200105 |
+------------+----------------+---------------+
1 row in set (0.01 sec)

二使用时间函数获取系统当前时间

mysql> SELECT CURTIME(),CURRENT_TIME(),CURTIME() + 0;
+-----------+----------------+---------------+
| CURTIME() | CURRENT_TIME() | CURTIME() + 0 |
+-----------+----------------+---------------+
| 14:52:34  | 14:52:34       |        145234 |
+-----------+----------------+---------------+
1 row in set (0.00 sec)

三使用日期时间函数获取当前系统日期和时间

mysql> SELECT CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | LOCALTIME()         | NOW()               | SYSDATE()           |
+---------------------+---------------------+---------------------+---------------------+
| 2020-01-05 14:53:14 | 2020-01-05 14:53:14 | 2020-01-05 14:53:14 | 2020-01-05 14:53:14 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.02 sec)

四使用UNIX_TIMESTAMP函数返回UNIX格式的时间戳

mysql> SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()), NOW();
+------------------+-----------------------+---------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP(NOW()) | NOW()               |
+------------------+-----------------------+---------------------+
|       1578207219 |            1578207219 | 2020-01-05 14:53:39 |
+------------------+-----------------------+---------------------+
1 row in set (0.00 sec)

五使用FROM_UNIXTIME函数将UNIX时间戳转换为普通格式时间

mysql> SELECT FROM_UNIXTIME('1364098609');
+-----------------------------+
| FROM_UNIXTIME('1364098609') |
+-----------------------------+
| 2013-03-24 12:16:49.000000  |
+-----------------------------+
1 row in set (0.02 sec)

六使用UTC_DATE()函数返回当前UTC日期值

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
+------------+----------------+
| UTC_DATE() | UTC_DATE() + 0 |
+------------+----------------+
| 2020-01-05 |       20200105 |
+------------+----------------+
1 row in set (0.00 sec)

七使用UTC_TIME()函数返回当前UTC时间值

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
+------------+----------------+
| UTC_TIME() | UTC_TIME() + 0 |
+------------+----------------+
| 06:55:05   |          65505 |
+------------+----------------+
1 row in set (0.00 sec)

八使用MONTH()函数返回指定日期中的月份

mysql> SELECT MONTH('2013-02-13');
+---------------------+
| MONTH('2013-02-13') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

九使用MONTHNAME()函数返回指定日期中的月份的名称

mysql> SELECT MONTHNAME('2013-02-13');
+-------------------------+
| MONTHNAME('2013-02-13') |
+-------------------------+
| February                |
+-------------------------+
1 row in set (0.01 sec)

十使用DAYNAME()函数返回指定日期的工作日名称

mysql> SELECT DAYNAME('2013-02-13');
+-----------------------+
| DAYNAME('2013-02-13') |
+-----------------------+
| Wednesday             |
+-----------------------+
1 row in set (0.00 sec)

十一使用DAYOFWEEK()函数返回日期对应的周索引

mysql> SELECT DAYOFWEEK('2013-02-13');   // 这里周三的索引是4，周日的索引为1，周六的索引是7
+-------------------------+
| DAYOFWEEK('2013-02-13') |
+-------------------------+
|                       4 |
+-------------------------+
1 row in set (0.00 sec)

十二使用WEEKDAY()函数返回日期对应的工作日索引

mysql> SELECT WEEKDAY('2011-02-13 22:23:00'), WEEKDAY('2011-07-01');   // 这里周一的索引是0，周日的索引是6
+--------------------------------+-----------------------+
| WEEKDAY('2011-02-13 22:23:00') | WEEKDAY('2011-07-01') |
+--------------------------------+-----------------------+
|                              6 |                     4 |
+--------------------------------+-----------------------+
1 row in set (0.00 sec)

十三使用WEEK()函数查询指定日期是一年中的第几周

mysql> SELECT WEEK('2011-02-20'),WEEK('2011-02-20',0), WEEK('2011-02-20',1);
+--------------------+----------------------+----------------------+
| WEEK('2011-02-20') | WEEK('2011-02-20',0) | WEEK('2011-02-20',1) |
+--------------------+----------------------+----------------------+
|                  8 |                    8 |                    7 |
+--------------------+----------------------+----------------------+
1 row in set (0.00 sec)

十四使用WEEKOFYEAR()查询指定日期是一年中的第几周

mysql> SELECT WEEK('2011-02-20',3), WEEKOFYEAR('2011-02-20');
+----------------------+--------------------------+
| WEEK('2011-02-20',3) | WEEKOFYEAR('2011-02-20') |
+----------------------+--------------------------+
|                    7 |                        7 |
+----------------------+--------------------------+
1 row in set (0.00 sec)

十五使用DAYOFYEAR()函数返回指定日期在一年中的位置

mysql> SELECT DAYOFYEAR('2011-02-20');
+-------------------------+
| DAYOFYEAR('2011-02-20') |
+-------------------------+
|                      51 |
+-------------------------+
1 row in set (0.00 sec)

十六使用DAYOFYEAR()函数返回指定日期在一个月中的位置

mysql> SELECT DAYOFMONTH('2011-02-20');
+--------------------------+
| DAYOFMONTH('2011-02-20') |
+--------------------------+
|                       20 |
+--------------------------+
1 row in set (0.00 sec)

十七使用YEAR()函数返回指定日期对应的年份

mysql>   SELECT YEAR('11-02-03'),YEAR('96-02-03');
+------------------+------------------+
| YEAR('11-02-03') | YEAR('96-02-03') |
+------------------+------------------+
|             2011 |             1996 |
+------------------+------------------+
1 row in set (0.00 sec)

十八使用QUARTER()函数返回指定日期对应的季度

mysql> SELECT QUARTER('11-04-01');
+---------------------+
| QUARTER('11-04-01') |
+---------------------+
|                   2 |
+---------------------+
1 row in set (0.00 sec)

十九使用MINUTE()函数返回指定时间的分钟值

mysql> SELECT MINUTE('11-02-03 10:11:03');
+-----------------------------+
| MINUTE('11-02-03 10:11:03') |
+-----------------------------+
|                          11 |
+-----------------------------+
1 row in set (0.00 sec)

二十使用SECOND()函数返回指定时间的秒值

mysql> SELECT SECOND('10:05:03');
+--------------------+
| SECOND('10:05:03') |
+--------------------+
|                  3 |
+--------------------+
1 row in set (0.00 sec)

二一使用EXTRACT函数提取日期或者时间值

SELECT EXTRACT(YEAR FROM '2011-07-02') AS col1,
     EXTRACT(YEAR_MONTH FROM '2011-07-12 01:02:03') AS col2,
     EXTRACT(DAY_MINUTE FROM '2011-07-12 01:02:03') AS col3;
+------+--------+--------+
| col1 | col2   | col3   |
+------+--------+--------+
| 2011 | 201107 | 120102 |
+------+--------+--------+
1 row in set (0.00 sec)

二二使用TIME_TO_SEC函数将时间值转换为秒值

mysql> SELECT TIME_TO_SEC('23:23:00');
+-------------------------+
| TIME_TO_SEC('23:23:00') |
+-------------------------+
|                   84180 |
+-------------------------+
1 row in set (0.00 sec)

二三使用SEC_TO_TIME()函数将秒值转换为时间格式

SELECT SEC_TO_TIME(2345),SEC_TO_TIME(2345)+0,
     TIME_TO_SEC('23:23:00'), SEC_TO_TIME(84180);
+-------------------+---------------------+-------------------------+--------------------+
| SEC_TO_TIME(2345) | SEC_TO_TIME(2345)+0 | TIME_TO_SEC('23:23:00') | SEC_TO_TIME(84180) |
+-------------------+---------------------+-------------------------+--------------------+
| 00:39:05          |                3905 |                   84180 | 23:23:00           |
+-------------------+---------------------+-------------------------+--------------------+
1 row in set (0.00 sec)

二四使用DATE_ADD()和ADDDATE()函数执行日期加操作

SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col1,
     ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND) AS col2,
     DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) AS col3;
+---------------------+---------------------+---------------------+
| col1                | col2                | col3                |
+---------------------+---------------------+---------------------+
| 2011-01-01 00:00:00 | 2011-01-01 00:00:00 | 2011-01-01 00:01:00 |
+---------------------+---------------------+---------------------+

二五使用DATE_SUB和SUBDATE函数执行日期减操作

SELECT DATE_SUB('2011-01-02', INTERVAL 31 DAY) AS col1,
     SUBDATE('2011-01-02', INTERVAL 31 DAY) AS col2,
     DATE_SUB('2011-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND) AS col3;
+------------+------------+---------------------+
| col1       | col2       | col3                |
+------------+------------+---------------------+
| 2010-12-02 | 2010-12-02 | 2010-12-31 23:59:59 |
+------------+------------+---------------------+
1 row in set (0.00 sec)

二六使用ADDTIME进行时间加操作

mysql> SELECT ADDTIME('2000-12-31 23:59:59','1:1:1'), ADDTIME('02:02:02', '02:00:00');
+----------------------------------------+---------------------------------+
| ADDTIME('2000-12-31 23:59:59','1:1:1') | ADDTIME('02:02:02', '02:00:00') |
+----------------------------------------+---------------------------------+
| 2001-01-01 01:01:00                    | 04:02:02                        |
+----------------------------------------+---------------------------------+
1 row in set (0.00 sec)

二七使用SUBTIME()函数执行时间减操作

mysql> SELECT SUBTIME('2000-12-31 23:59:59','1:1:1'), SUBTIME('02:02:02','02:00:00');
+----------------------------------------+--------------------------------+
| SUBTIME('2000-12-31 23:59:59','1:1:1') | SUBTIME('02:02:02','02:00:00') |
+----------------------------------------+--------------------------------+
| 2000-12-31 22:58:58                    | 00:02:02                       |
+----------------------------------------+--------------------------------+
1 row in set (0.00 sec)

二八使用DATEDIFF()函数计算两个日期之间的间隔天数

SELECT DATEDIFF('2010-12-31 23:59:59','2010-12-30') AS col1,
     DATEDIFF('2010-11-30 23:59:59','2010-12-31') AS col2;
+------+------+
| col1 | col2 |
+------+------+
|    1 |  -31 |
+------+------+
1 row in set (0.01 sec)

二九使用DATE_FORMAT()函数格式化输出日期和时间值

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS col1,DATE_FORMAT('1997-10-04 22:23:00','%D %y %a %d %m %b %j') AS col2;
+-----------------------+--------------------------+
| col1                  | col2                     |
+-----------------------+--------------------------+
| Saturday October 1997 | 4th 97 Sat 04 10 Oct 277 |
+-----------------------+--------------------------+
1 row in set (0.01 sec)

三十使用TIME_FORMAT()函数格式化输入时间值

mysql> SELECT TIME_FORMAT('16:00:00', '%H %k %h %I %l');
+-------------------------------------------+
| TIME_FORMAT('16:00:00', '%H %k %h %I %l') |
+-------------------------------------------+
| 16 16 04 04 4                             |
+-------------------------------------------+
1 row in set (0.00 sec)

三一使用GET_FORMAT()函数显示不同格式化类型下的格式字符串

mysql> SELECT GET_FORMAT(DATE,'EUR'), GET_FORMAT(DATE,'USA');
+------------------------+------------------------+
| GET_FORMAT(DATE,'EUR') | GET_FORMAT(DATE,'USA') |
+------------------------+------------------------+
| %d.%m.%Y               | %m.%d.%Y               |
+------------------------+------------------------+
1 row in set (0.01 sec)

三二在DATE_FORMAT()函数中，使用GET_FORMAT函数返回的显示格式字符串来显示指定的日期值

mysql> SELECT DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA') );
+-------------------------------------------------------------+
| DATE_FORMAT('2000-10-05 22:23:00', GET_FORMAT(DATE,'USA') ) |
+-------------------------------------------------------------+
| 10.05.2000                                                  |
+-------------------------------------------------------------+
1 row in set (0.00 sec)