mysql中 常用的时间函数

天数函数：

星期函数：

 今天星期几：

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中 常用的时间函数

标签：