mysql中 常用的時間函數

標籤：

時間函數：

now：目前時間帶格式

mysql> select now();+---------------------+| now()               |+---------------------+| 2015-09-06 10:47:33 |+---------------------+1 row in set (0.00 sec)

 

to_days：給定一個日期date, 返回一個天數 (從年份0開始的天數 )。

mysql> select TO_DAYS(NOW());+----------------+| TO_DAYS(NOW()) |+----------------+|         736212 |+----------------+1 row in set (0.00 sec)

今天插入的條數：

mysql> select count(*) from black_list where to_days(now()) - to_days(ctime) <1;+----------+| count(*) |+----------+|    38089 |+----------+1 row in set (1.93 sec)

年數函數：

 

天數函數：

星期函數：

 今天星期幾：

DAYOFWEEK  1=》星期日；2=》星期一；3=》星期二。。。7=》星期六mysql> select DAYOFWEEK(now());+------------------+| DAYOFWEEK(now()) |+------------------+|                1 |+------------------+1 row in set (0.00 sec)weekday  0=》星期一；1=》星期二；2=》星期三。。。6=》星期日mysql> select weekday(now());+----------------+| weekday(now()) |+----------------+|              6 |+----------------+1 row in set (0.00 sec)

 今天幾號：dayofmonth：

mysql> select dayofmonth(now());+-------------------+| dayofmonth(now()) |+-------------------+|                 6 |+-------------------+1 row in set (0.00 sec)

今天是今年中的第幾天：dayofyear

mysql> select dayofyear(now());+------------------+| dayofyear(now()) |+------------------+|              249 |+------------------+1 row in set (0.00 sec)

查詢月份：

mysql> select month(now());+--------------+| month(now()) |+--------------+|            9 |+--------------+1 row in set (0.00 sec)　

一年中的第幾周（從0開始）有第二個參數1就從1開始：week(now(),1)

mysql> select week("2015-01-10");+--------------------+| week("2015-01-10") |+--------------------+|                  1 |+--------------------+1 row in set (0.00 sec)mysql> select week("2015-01-11");+--------------------+| week("2015-01-11") |+--------------------+|                  2 |+--------------------+1 row in set (0.00 sec)mysql> select week("2015-01-1");+-------------------+| week("2015-01-1") |+-------------------+|                 0 |+-------------------+

　　

星期幾的英文：

mysql> select dayname(now());+----------------+| dayname(now()) |+----------------+| Sunday         |+----------------+1 row in set (0.00 sec)

月份的英文

mysql> select monthname(now());+------------------+| monthname(now()) |+------------------+| September        |+------------------+1 row in set (0.00 sec)

　　

 季度（1-4）

mysql> select QUARTER(now());+----------------+| QUARTER(now()) |+----------------+|              3 |+----------------+1 row in set (0.00 sec)

年月日時分秒：

mysql> select year(now());+-------------+| year(now()) |+-------------+|        2015 |+-------------+1 row in set (0.00 sec)mysql> select month(now());+--------------+| month(now()) |+--------------+|            9 |+--------------+1 row in set (0.00 sec)mysql> select day(now());+------------+| day(now()) |+------------+|          6 |+------------+1 row in set (0.00 sec)mysql> select hour(now());+-------------+| hour(now()) |+-------------+|          11 |+-------------+1 row in set (0.00 sec)mysql> select minute(now());+---------------+| minute(now()) |+---------------+|            41 |+---------------+1 row in set (0.00 sec)mysql> select second(now());+---------------+| second(now()) |+---------------+|            20 |+---------------+1 row in set (0.00 sec)

　增加日期：第一個參數時間（2015-09-06 11:46:37），第二個參數 Interval 2 year 、Interval 2 week、Interval 2 day、Interval 2 hour、minute、second

mysql> select DATE_ADD(now(), Interval 2 year);+----------------------------------+| DATE_ADD(now(), Interval 2 year) |+----------------------------------+| 2017-09-06 11:46:37              |+----------------------------------+1 row in set (0.00 sec)

　　

日期格式化：

DATE_FORMAT(date,format)  
根據format字串格式化date值。下列修飾符可以被用在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 date_format(now(),"%M");+-------------------------+| date_format(now(),"%M") |+-------------------------+| September               |+-------------------------+1 row in set (0.00 sec)mysql> select date_format(now(),"%m");+-------------------------+| date_format(now(),"%m") |+-------------------------+| 09                      |+-------------------------+1 row in set (0.00 sec)mysql> select date_format(now(),"%D");+-------------------------+| date_format(now(),"%D") |+-------------------------+| 6th                     |+-------------------------+1 row in set (0.00 sec)mysql> select date_format(now(),"%d");+-------------------------+| date_format(now(),"%d") |+-------------------------+| 06                      |+-------------------------+1 row in set (0.00 sec)mysql> select date_format("2015-8-28","%d");+-------------------------------+| date_format("2015-8-28","%d") |+-------------------------------+| 28                            |+-------------------------------+1 row in set (0.00 sec)mysql> select date_format("2015-8-28","%m-%d");+----------------------------------+| date_format("2015-8-28","%m-%d") |+----------------------------------+| 08-28                            |+----------------------------------+1 row in set (0.00 sec)

　　

 

 

 

 

 

 

 

 

 

 

（待續）

mysql中 常用的時間函數