轉：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)