MySQL的日期/時間函數
一、 Mysql 獲得當前日期時間
Now() : 獲得當前的 日期+ 時間（date + time ）函數：
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-03-01 10:26:23 |
+---------------------+
1 row in set (0.00 sec)
同等的函數還包括current_timestamp(),localtime() ，但是now() 最容易記憶，所以推薦使用。
Sysdate() : 日期時間函數跟 now() 類似，不同之處在于：now() 在執行開始時值就得到了， sysdate() 在函數執行時動態得到值。
看下面的例子就明白了：
mysql> select now(),sleep(3),now();
+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2011-03-01 10:51:43 | 0 | 2011-03-01 10:51:43 |
+---------------------+----------+---------------------+
1 row in set (3.02 sec)
mysql> select sysdate(),sleep(3),sysdate();
+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
| 2011-03-01 10:52:09 | 0 | 2011-03-01 10:52:12 |
+---------------------+----------+---------------------+
1 row in set (3.00 sec)
可以看到，雖然中途 sleep 3 秒，但 now() 函數兩次的時間值是相同的； sysdate() 函數兩次得到的時間值相差 3 秒
也可以只取當前日期或者當前時間
Curdate(): 獲得當前日期
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2011-03-01 |
+------------+
1 row in set (0.01 sec)
Curtime() : 獲得當前時間（time ）函數
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 10:55:12 |
+-----------+
1 row in set (0.00 sec)
二、 Mysql 日期時間的抽取extract
通過這一功能，我們可以從一個時間中抽取自己想要的部分，例如
mysql> set @ct='2011-03-01 11:16:14.123456';
Query OK, 0 rows affected (0.01 sec)
設置變量ct 為某一時間值，精確到微妙
獲取其日期值
mysql> select date(@ct);
+------------+
| date(@ct) |
+------------+
| 2011-03-01 |
+------------+
1 row in set (0.00 sec)
查看此日期所屬季度
mysql> select quarter(@ct);
+--------------+
| quarter(@ct) |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
查看此日期屬于當年哪一周
mysql> select week(@ct);
+-----------+
| week(@ct) |
+-----------+
| 9 |
+-----------+
1 row in set (0.00 sec)
另外還有year(),day(),hour(),minute(),second() 等，在此不一一贅述。
采用extract() ，也可以實現類似的功能，語法格式為extract(year from @ct) ，
不足之處在于需要多敲幾次鍵盤
Dayof 函數 :
Dayofweek(),dayofmonth(),dayofyear() 分別返回日期再一周、一月以及一年中的位置
mysql> select dayofweek(@ct);
+----------------+
| dayofweek(@ct) |
+----------------+
| 3 |
+----------------+
1 row in set (0.00 sec)
注意：其實3 月1 號是星期二，但是返回數字3 ，因為是從Sunday 開始算起的(1=Sunday,2=Monday, …)
mysql> select dayofmonth(@ct);
+-----------------+
| dayofmonth(@ct) |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.00 sec)
mysql> select dayofyear(@ct);
+----------------+
| dayofyear(@ct) |
+----------------+
| 60 |
+----------------+
1 row in set (0.00 sec)
Week() 函數
查看日期屬于當年的第幾周
mysql> select weekofyear(@ct);
+-----------------+
| weekofyear(@ct) |
+-----------------+
| 9 |
+-----------------+
1 row in set (0.00 sec)
返回星期名和月份名的函數
Dayname() —計算日期是星期幾
mysql> select dayname(@ct);
+--------------+
| dayname(@ct) |
+--------------+
| Tuesday |
+--------------+
1 row in set (0.02 sec)
Monthname() —計算日期是哪一月
mysql> select monthname(@ct);
+----------------+
| monthname(@ct) |
+----------------+
| March |
+----------------+
1 row in set (0.00 sec)
Last_day() : 返回月份中最后一天
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2011-03-01 13:15:00 |
+---------------------+
1 row in set (0.00 sec)
mysql> select last_day(now());
+-----------------+
| last_day(now()) |
+-----------------+
| 2011-03-31 |
+-----------------+
1 row in set (0.00 sec)
通過該函數，可以計算出當前月份有多少天
mysql> select now(),day(last_day(now())) ;
+---------------------+----------------------+
| now() | day(last_day(now())) |
+---------------------+----------------------+
| 2011-03-01 13:17:12 | 31 |
+---------------------+----------------------+
1 row in set (0.00 sec)
三、Mysql 的日期時間計算函數
Date_add() : 為日期增加一個時間間隔
具體語法為date_add(@ct, interval num year/quarter/month/week/day/hour/minute/secont/microsecond);
注意: 此函數并不改變變量@ct 的實際值
mysql> set @ct=now();
Query OK, 0 rows affected (0.00 sec)
mysql> select @ct;
+---------------------+
| @ct |
+---------------------+
| 2011-03-01 15:09:16 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date_add(@ct,interval 1 day);
+------------------------------+
| date_add(@ct,interval 1 day) |
+------------------------------+
| 2011-03-02 15:09:16 |
+------------------------------+
1 row in set (0.00 sec)
mysql> select @ct;
+---------------------+
| @ct |
+---------------------+
| 2011-03-01 15:09:16 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date_add(@ct,interval 1 week);
+-------------------------------+
| date_add(@ct,interval 1 week) |
+-------------------------------+
| 2011-03-08 15:09:16 |
+-------------------------------+
1 row in set (0.00 sec)
類似功能還有adddate(),addtime() 等函數，與之相對應的是date_sub() ，顧名思義就是日期減法
另類日期函數
Period_add(P,N): 日期加/ 減去N 月，其中P 的格式應為yyyymm 或yymm
Period_diff(P1,P2): 日期p1-p2 ，返回N 個月
mysql> select period_add(201103,2),period_add(201103,-2) ;
+----------------------+-----------------------+
| period_add(201103,2) | period_add(201103,-2) |
+----------------------+-----------------------+
| 201105 | 201101 |
+----------------------+-----------------------+
1 row in set (0.00 sec)
mysql> select period_diff('201103','201101');
+--------------------------------+
| period_diff('201103','201101') |
+--------------------------------+
| 2 |
+--------------------------------+
1 row in set (0.00 sec)
日期時間相減函數
Datediff(date1,date2) : 兩個日期date1-date2
mysql> select datediff('2011-03-09','2011-03-01');
+-------------------------------------+
| datediff('2011-03-09','2011-03-01') |
+-------------------------------------+
| 8 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2011-03-01','2011-03-09');
+-------------------------------------+
| datediff('2011-03-01','2011-03-09') |
+-------------------------------------+
| -8 |
+-------------------------------------+
1 row in set (0.00 sec)
Timediff(time1,time2) : 兩個時間相減
mysql> select timediff('2011-03-03 15:33:00','2011-03-02 15:33:59');
+-------------------------------------------------------+
| timediff('2011-03-03 15:33:00','2011-03-02 15:33:59') |
+-------------------------------------------------------+
| 23:59:01 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select timediff('15:33:00','15:33:59');
+---------------------------------+
| timediff('15:33:00','15:33:59') |
+---------------------------------+
| -00:00:59 |
+---------------------------------+
1 row in set (0.00 sec)
四 mysql 日期、時間轉換函數
Time_to_sec(time) : 時間—> 秒 轉換函數
Sec_to_time(num) : 秒--> 時間 轉換函數
mysql> select time_to_sec('01:00:00');
+-------------------------+
| time_to_sec('01:00:00') |
+-------------------------+
| 3600 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select sec_to_time(3600);
+-------------------+
| sec_to_time(3600) |
+-------------------+
| 01:00:00 |
+-------------------+
1 row in set (0.00 sec)
To_days(date) : 日期--> 天 轉換函數 起始日期為0000-00-00
From_days(num) : 天--> 日期 將數字轉換為具體的日期
mysql> select to_days('2011-03-01');
+-----------------------+
| to_days('2011-03-01') |
+-----------------------+
| 734562 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select from_days(734562);
+-------------------+
| from_days(734562) |
+-------------------+
| 2011-03-01 |
+-------------------+
1 row in set (0.00 sec)
Str_to_date(str,date) : 字符串--> 日期 轉換函數
可以將一些雜亂無章的字符轉換為日期格式
mysql> select str_to_date('01.03.2011', '%m.%d.%Y');
+---------------------------------------+
| str_to_date('01.03.2011', '%m.%d.%Y') |
+---------------------------------------+
| 2011-01-03 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select str_to_date('01/03/2011', '%m/%d/%Y');
+---------------------------------------+
| str_to_date('01/03/2011', '%m/%d/%Y') |
+---------------------------------------+
| 2011-01-03 |
+---------------------------------------+
1 row in set (0.00 sec)
小練習：
以表centralmobile_logs 為例，目前該表總共有270 多萬條數據
mysql> select count(*) from centralmobile_logs;
+----------+
| count(*) |
+----------+
| 2725403 |
+----------+
1 row in set (0.00 sec)
現在對其做一些統計
查詢過去30 天總共有多少數據
mysql> select count(*) from centralmobile_logs where to_days(curdate())- to_days(create_time)<=30;
+----------+
| count(*) |
+----------+
| 2367518 |
+----------+
1 row in set (3.38 sec)
mysql> select count(*) from centralmobile_logs where datediff(curdate(),create_time) <=30;
+----------+
| count(*) |
+----------+
| 2367518 |
+----------+
1 row in set (3.29 sec)
查看每月第一天的數據
mysql> select count(*) from centralmobile_logs where dayofmonth(create_time)=1;
+----------+
| count(*) |
+----------+
| 161293 |
+----------+
1 row in set (3.14 sec)
查看11 年1 月31 日之前的數據
mysql> select count(*) from centralmobile_logs where create_time <='2011-01-31 00:00:00';
+----------+
| count(*) |
+----------+
| 413797 |
+----------+
1 row in set (0.17 sec)
查看11 年整個二月份的數據
mysql> select count(*) from centralmobile_logs where monthname(create_time)='February' and year(create_time)=2011;
+----------+
| count(*) |
+----------+
| 2149284 |
+----------+
1 row in set (3.94 sec)
查看11 年每個周日的累積數據
mysql> select count(*) from centralmobile_logs where dayname(create_time)='Sunday' and year(create_time)=2011;
+----------+
| count(*) |
+----------+
| 479033 |
+----------+
1 row in set (3.88 sec)
查看每天零點時分插入的數據總和
mysql> select count(*) from centralmobile_logs where time(create_time)='00:00:00';
+----------+
| count(*) |
+----------+
| 37 |
+----------+
1 row in set (3.99 sec)