1. 日期相減
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1
1.1 更多日期相減
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
-> 128885
2.統計值
AVG(): 平均值, STDDEV(): 標準差
3.日期加減時間
加一天
DATE_ADD(date, INTERVAL 1 DAY)
減一天
DATE_SUB(date, INTERVAL 1 DAY)
其他時間格式
SECOND ss
MINUTE mm
HOUR hh
DAY DD
MONTH MM
YEAR YY
MINUTE_SECOND mm:ss
HOUR_MINUTE hh:mm
DAY_HOUR DD:hh
YEAR_MONTH YYYY-MM
HOUR_SECOND hh:ss
DAY_MINUTE DD hh:mm
DAY_SECOND DD hh:mm:ss
4.取年、月、日
select year(now())
select month(now())
select day(now())
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1
1.1 更多日期相減
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
-> 128885
2.統計值
AVG(): 平均值, STDDEV(): 標準差
3.日期加減時間
加一天
DATE_ADD(date, INTERVAL 1 DAY)
減一天
DATE_SUB(date, INTERVAL 1 DAY)
其他時間格式
SECOND ss
MINUTE mm
HOUR hh
DAY DD
MONTH MM
YEAR YY
MINUTE_SECOND mm:ss
HOUR_MINUTE hh:mm
DAY_HOUR DD:hh
YEAR_MONTH YYYY-MM
HOUR_SECOND hh:ss
DAY_MINUTE DD hh:mm
DAY_SECOND DD hh:mm:ss
4.取年、月、日
select year(now())
select month(now())
select day(now())