mysql 时间函数

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