Mysql 日期时间操作

时间戳

mysql> SELECT UNIX_TIMESTAMP(), FROM_UNIXTIME(UNIX_TIMESTAMP());

UNIX_TIMESTAMP()|FROM_UNIXTIME(UNIX_TIMESTAMP())|
----------------+-------------------------------+
1630134918      |2021-08-28 07:15:18.0          |

日期时间查询

当前日期时间查询

mysql> SELECT CURRENT_TIMESTAMP, NOW(), LOCALTIME, LOCALTIMESTAMP, SYSDATE();

CURRENT_TIMESTAMP    |NOW()                |LOCALTIME            |LOCALTIMESTAMP       |SYSDATE()            |
---------------------+---------------------+---------------------+---------------------+---------------------+
2021-08-28 07:05:43.0|2021-08-28 07:05:43.0|2021-08-28 07:05:43.0|2021-08-28 07:05:43.0|2021-08-28 07:05:43.0|

当前日期查询

mysql> SELECT CURRENT_DATE, CURDATE();

CURRENT_DATE|CURDATE() |
------------+----------+
2021-08-28  |2021-08-28|

当前时间查询

mysql> SELECT CURRENT_TIME, CURTIME();

CURRENT_TIME|CURTIME()|
------------+---------+
07:08:23    |07:08:23 |

当前 UTC 日期时间查询

mysql> SELECT UTC_TIMESTAMP, UTC_DATE, UTC_TIME;

UTC_TIMESTAMP        |UTC_DATE  |UTC_TIME|
---------------------+----------+--------+
2021-08-28 07:34:51.0|2021-08-28|07:34:51|

日期时间提取

mysql> SELECT TIMESTAMP(CURRENT_DATE), FROM_UNIXTIME(UNIX_TIMESTAMP());

TIMESTAMP(CURRENT_DATE)|FROM_UNIXTIME(UNIX_TIMESTAMP())|
-----------------------+-------------------------------+
2021-08-28 00:00:00.0  |2021-08-28 07:14:40.0          |

日期提取

mysql> SELECT DATE(NOW()), YEAR(NOW()), YEARWEEK(NOW()), QUARTER(NOW()), MONTH(NOW()), WEEK(NOW()), DAY(NOW()), MONTHNAME(NOW());

DATE(NOW())|YEAR(NOW())|YEARWEEK(NOW())|QUARTER(NOW())|MONTH(NOW())|WEEK(NOW())|DAY(NOW())|MONTHNAME(NOW())|
-----------+-----------+---------------+--------------+------------+-----------+----------+----------------+
2023-11-29 |2023       |202348         |4             |11          |48         |29        |November        |

mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m'),  EXTRACT(YEAR_MONTH FROM NOW());

DATE_FORMAT(NOW(), '%Y-%m')|EXTRACT(YEAR_MONTH FROM NOW())|
---------------------------+------------------------------+
2023-11                    |202311                        |

时间提取

mysql> SELECT TIME(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()), MICROSECOND(NOW());

TIME(NOW())|HOUR(NOW())|MINUTE(NOW())|SECOND(NOW())|MICROSECOND(NOW())|
-----------+-----------+-------------+-------------+------------------+
07:10:28   |7          |10           |28           |0                 |

星期提取

mysql> SELECT WEEK(NOW(), 0), WEEKOFYEAR(NOW()), WEEKDAY(NOW()), DAYOFWEEK(NOW()), YEARWEEK(NOW()), DAYNAME(NOW());

WEEK(NOW(), 0)|WEEKOFYEAR(NOW())|WEEKDAY(NOW())|DAYOFWEEK(NOW())|YEARWEEK(NOW())|DAYNAME(NOW())|
--------------+-----------------+--------------+----------------+---------------+--------------+
34            |34               |5             |7               |202134         |Saturday      |

日期时间转换

mysql> SELECT STR_TO_DATE('08/28/2021', '%m/%d/%Y'), SEC_TO_TIME(60*60), TIME_TO_SEC('23:59:59');

STR_TO_DATE('08/28/2021', '%m/%d/%Y')|SEC_TO_TIME(60*60)|TIME_TO_SEC('23:59:59')|
-------------------------------------+------------------+-----------------------+
2021-08-28                           |01:00:00          |86399                  |

mysql> SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S'), TIME_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S');

DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')|TIME_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S')|
---------------------------------------|---------------------------------------|
2022-03-09 21:39:30                    |2022-03-09 21:39:30                    |

日期时间计算

mysql> SELECT TO_DAYS('0000-01-01'), DAYOFYEAR(NOW()), DAYOFMONTH(NOW()), DAYOFWEEK(NOW()), LAST_DAY(NOW());

TO_DAYS('0000-01-01')|DAYOFYEAR(NOW())|DAYOFMONTH(NOW())|DAYOFWEEK(NOW())|LAST_DAY(NOW())|
---------------------+----------------+-----------------+----------------+---------------+
1                    |240             |28               |7               |2021-08-31     |

日期时间加减

mysql> SELECT TIMESTAMPADD(YEAR , -1, NOW()), TIMESTAMPDIFF(HOUR, CURRENT_TIMESTAMP, UTC_TIMESTAMP);

TIMESTAMPADD(YEAR , -1, NOW())|TIMESTAMPDIFF(HOUR, CURRENT_TIMESTAMP, UTC_TIMESTAMP)|
------------------------------+-----------------------------------------------------+
2020-08-28 16:07:26.0         |-8                                                   |

日期加减

mysql> SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR), DATE_SUB(NOW(), INTERVAL 1 YEAR), ADDDATE(NOW(), INTERVAL 1 YEAR), SUBDATE(NOW(), INTERVAL 1 YEAR), DATEDIFF(CURRENT_TIMESTAMP, UTC_TIMESTAMP);

DATE_ADD(NOW(), INTERVAL 1 YEAR)|DATE_SUB(NOW(), INTERVAL 1 YEAR)|ADDDATE(NOW(), INTERVAL 1 YEAR)|SUBDATE(NOW(), INTERVAL 1 YEAR)|DATEDIFF(CURRENT_TIMESTAMP, UTC_TIMESTAMP)|
--------------------------------+--------------------------------+-------------------------------+-------------------------------+------------------------------------------+
2022-08-28 16:08:17.0           |2020-08-28 16:08:17.0           |2022-08-28 16:08:17.0          |2020-08-28 16:08:17.0          |0                                         |

mysql> SELECT PERIOD_ADD('2108', '1'), PERIOD_DIFF('2108', '2107');

PERIOD_ADD('2108', '1')|PERIOD_DIFF('2108', '2107')|
-----------------------+---------------------------+
202109                 |1                          |

时间加减

mysql> SELECT ADDTIME(NOW(), '1:1:1.1'), SUBTIME(NOW(), '1:1:1.0'), TIMEDIFF(CURRENT_TIMESTAMP, UTC_TIMESTAMP);

ADDTIME(NOW(), '1:1:1.1')|SUBTIME(NOW(), '1:1:1.0')|TIMEDIFF(CURRENT_TIMESTAMP, UTC_TIMESTAMP)|
-------------------------+-------------------------+------------------------------------------+
2021-08-28 17:09:53.1    |2021-08-28 15:07:51.0    |08:00:00                                  |