mysql 时间函数

最新推荐文章于 2025-08-16 15:45:23 发布

原创最新推荐文章于 2025-08-16 15:45:23 发布 · 622 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #date #数据库 #2010

mysql数据库操作中遇到但是不常用的sql语句，收集下方便日后使用

1. 时间比较函数

SELECT TIMESTAMPDIFF(MONTH,'2009-10-01','2009-09-01');

SELECT TIMESTAMPDIFF(YEAR,'2009-05-01','2008-01-01');

2. 时间转换函数

mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'

mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%w %m %y');
-> ' 6 10 97 '

3. 时间比较效率高不需遍历库

mysql> SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;
-> 1998-01-01 00:00:00
mysql> SELECT INTERVAL 1 DAY + "1997-12-31";
-> 1998-01-01
mysql> SELECT "1998-01-01" - INTERVAL 1 SECOND;
-> 1997-12-31 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",  INTERVAL 1 SECOND);
-> 1998-01-01 00:00:00
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",  INTERVAL 1 DAY);
-> 1998-01-01 23:59:59
mysql> SELECT DATE_ADD("1997-12-31 23:59:59",  INTERVAL "1:1" MINUTE_SECOND);
-> 1998-01-01 00:01:00
mysql> SELECT DATE_SUB("1998-01-01 00:00:00",  INTERVAL "1 1:1:1" DAY_SECOND);
-> 1997-12-30 22:58:59
mysql> SELECT DATE_ADD("1998-01-01 00:00:00",  INTERVAL "-1 10" DAY_HOUR);
-> 1997-12-30 14:00:00
mysql> SELECT DATE_SUB("1998-01-02", INTERVAL 31 DAY);
-> 1997-12-02

4. 时间提取
mysql> SELECT EXTRACT(YEAR FROM "1999-07-02");
-> 1999
mysql> SELECT EXTRACT(YEAR_MONTH FROM "1999-07-02 01:02:03");
-> 199907
mysql> SELECT EXTRACT(DAY_MINUTE FROM "1999-07-02 01:02:03");
-> 20102

5. 整数型的当前时间

mysql>select UNIX_TIMESTAMP();

->1388736050