Mysql的時間函數

標籤：

mysql的時間函數，放著查看

1.本周內的第幾天，從周日開始mysql> select dayofweek(‘2015-05-25‘);+-------------------------+| dayofweek(‘2015-05-25‘) |+-------------------------+|                       2 |+-------------------------+1 row in set (0.00 sec)2.本月內的第幾天mysql> select dayofmonth(‘2015-05-25‘);+--------------------------+| dayofmonth(‘2015-05-25‘) |+--------------------------+|                       25 |+--------------------------+1 row in set (0.00 sec)3.本年內的第幾天mysql> select dayofyear(‘2015-05-25‘);+-------------------------+| dayofyear(‘2015-05-25‘) |+-------------------------+|                     145 |+-------------------------+1 row in set (0.00 sec)4.擷取周幾mysql> select dayname(‘2015-05-26‘);+-----------------------+| dayname(‘2015-05-26‘) |+-----------------------+| Tuesday               |+-----------------------+1 row in set (0.04 sec)5.擷取月份mysql> select month(‘2015-05-26‘),monthname(‘2015-05-26‘);+---------------------+-------------------------+| month(‘2015-05-26‘) | monthname(‘2015-05-26‘) |+---------------------+-------------------------+|                   5 | May                     |+---------------------+-------------------------+1 row in set (0.00 sec)6.擷取第幾周mysql> select week(‘2015-05-26‘);+--------------------+| week(‘2015-05-26‘) |+--------------------+|                 21 |+--------------------+1 row in set (0.00 sec)7.擷取第幾季度mysql> select quarter(‘2015-06-26‘);+-----------------------+| quarter(‘2015-06-26‘) |+-----------------------+|                     2 |+-----------------------+1 row in set (0.00 sec)8.查看年份mysql> select year(‘2015-09-26‘);+--------------------+| year(‘2015-09-26‘) |+--------------------+|               2015 |+--------------------+1 row in set (0.00 sec)9.其他mysql> select year(‘2015-05-26‘),month(‘2015-05-26‘),day(‘2015-05-26‘);+--------------------+---------------------+-------------------+| year(‘2015-05-26‘) | month(‘2015-05-26‘) | day(‘2015-05-26‘) |+--------------------+---------------------+-------------------+|               2015 |                   5 |                26 |+--------------------+---------------------+-------------------+1 row in set (0.01 sec)mysql> select hour(‘10:05:03‘),minute(‘10:05:03‘),second(‘10:05:03‘);+------------------+--------------------+--------------------+| hour(‘10:05:03‘) | minute(‘10:05:03‘) | second(‘10:05:03‘) |+------------------+--------------------+--------------------+|               10 |                  5 |                  3 |+------------------+--------------------+--------------------+1 row in set (0.00 sec)10.查看目前時間mysql> select current_time(),sysdate(),current_timestamp(),now();+----------------+---------------------+---------------------+---------------------+| current_time() | sysdate()           | current_timestamp() | now()               |+----------------+---------------------+---------------------+---------------------+| 00:29:05       | 2015-05-26 00:29:05 | 2015-05-26 00:29:05 | 2015-05-26 00:29:05 |+----------------+---------------------+---------------------+---------------------+1 row in set (0.00 sec)其他類似內建函數，now是跟session有關的，sysdate無關事務current_timestamp(),current_timestamp,localtime(),localtime,localtimestamp,localtimestamp()11.unix時間戳記轉換mysql> select unix_timestamp(‘2015-05-26 15:38:22‘),unix_timestamp(now());+---------------------------------------+-----------------------+| unix_timestamp(‘2015-05-26 15:38:22‘) | unix_timestamp(now()) |+---------------------------------------+-----------------------+|                            1432679902 |            1432625945 |+---------------------------------------+-----------------------+1 row in set (0.00 sec)mysql> select from_unixtime(1432679902),from_unixtime(1432625945);+---------------------------+---------------------------+| from_unixtime(1432679902) | from_unixtime(1432625945) |+---------------------------+---------------------------+| 2015-05-26 15:38:22       | 2015-05-26 00:39:05       |+---------------------------+---------------------------+1 row in set (0.00 sec)12.format格式%M 月名字(January……December)%W 星期名字(Sunday……Saturday)%D 有英語首碼的月份的日期(1st, 2nd, 3rd, 等等。）%Y 年, 數字, 4 位%y 年, 數字, 2 位%a 縮寫的星期名字(Sun……Sat)%d 月份中的天數, 數字(00……31)%e 月份中的天數, 數字(0……31)%m 月, 數字(01……12)%c 月, 數字(1……12)%b 縮寫的月份名字(Jan……Dec)%j 一年中的天數(001……366)%H 小時(00……23)%k 小時(0……23)%h 小時(01……12)%I 小時(01……12)%l 小時(1……12)%i 分鐘, 數字(00……59)%r 時間,12 小時(hh:mm:ss [AP]M)%T 時間,24 小時(hh:mm:ss)%S 秒(00……59)%s 秒(00……59)%p AM或PM%w 一個星期中的天數(0=Sunday ……6=Saturday ）%U 星期(0……52), 這裡星期天是星期的第一天%u 星期(0……52), 這裡星期一是星期的第一天%% 字元%mysql>  select now(),DATE_FORMAT(now(),‘%M %D %Y %b %a %d %e %j %i‘);+---------------------+-------------------------------------------------+| now()               | DATE_FORMAT(now(),‘%M %D %Y %b %a %d %e %j %i‘) |+---------------------+-------------------------------------------------+| 2015-05-26 01:38:17 | May 26th 2015 May Tue 26 26 146 38              |+---------------------+-------------------------------------------------+1 row in set (0.00 sec)13.時間運算mysql> select now(),date_add(now(),interval 11 second);+---------------------+------------------------------------+| now()               | date_add(now(),interval 11 second) |+---------------------+------------------------------------+| 2015-05-27 00:22:20 | 2015-05-27 00:22:31                |+---------------------+------------------------------------+1 row in set (0.00 sec)mysql> select now(),date_add(now(),interval 1 hour);+---------------------+---------------------------------+| now()               | date_add(now(),interval 1 hour) |+---------------------+---------------------------------+| 2015-05-27 00:23:26 | 2015-05-27 01:23:26             |+---------------------+---------------------------------+1 row in set (0.00 sec)mysql> select now(),date_add(now(),interval 1 day);+---------------------+--------------------------------+| now()               | date_add(now(),interval 1 day) |+---------------------+--------------------------------+| 2015-05-27 00:22:28 | 2015-05-28 00:22:28            |+---------------------+--------------------------------+1 row in set (0.00 sec)mysql> select now(),date_add(now(),interval 1 month);+---------------------+----------------------------------+| now()               | date_add(now(),interval 1 month) |+---------------------+----------------------------------+| 2015-05-27 00:23:57 | 2015-06-27 00:23:57              |+---------------------+----------------------------------+1 row in set (0.00 sec)mysql> select now(),date_add(now(),interval -1 year);+---------------------+----------------------------------+| now()               | date_add(now(),interval -1 year) |+---------------------+----------------------------------+| 2015-05-27 00:24:34 | 2014-05-27 00:24:34              |+---------------------+----------------------------------+1 row in set (0.06 sec)

Mysql的時間函數