mysql时间日期函数例子-百分百源码网

mysql时间日期函数例子

发布时间：01/15 来源：浏览：关键词：

下面本文章给各位整理了不少的mysql时间日期函数例子了，下面我们一起来看看这些日期时间函数的一些应用例子，希望文章对各位会有帮助。

1.DAYOFWEEK(date);返回data时间是周几(1 => 星期日，2 => 星期一，3 => 星期二，……，7 => 星期六)。

mysql> select DAYOFWEEK('2013-06-09 14:00:00');
+----------------------------------+
| DAYOFWEEK('2013-06-09 14:00:00') |
+----------------------------------+
| 1 |
+----------------------------------+
2.DAYOFMONTH(date);返回date是一个月的第几天 1——31

mysql> select DAYOFMONTH('2013-06-09 14:00:00');
+-----------------------------------+
| DAYOFMONTH('2013-06-09 14:00:00') |
+-----------------------------------+
| 9 |
+-----------------------------------+
3.DAYOFYEAR(date);返回date是一年中的第几天 1———366

mysql> select DAYOFYEAR('2013-06-09 14:00:00');
+----------------------------------+
| DAYOFYEAR('2013-06-09 14:00:00') |
+----------------------------------+
|160 |
+----------------------------------+
4.WEEKDAY(date);返回date是周几(0 => 星期一，1 => 星期二，2 => 星期三，……, 6=> 星期日)。

mysql> select WEEKDAY('2013-06-09 14:00:00');
+--------------------------------+
| WEEKDAY('2013-06-09 14:00:00') |
+--------------------------------+
|6 |
+--------------------------------+
5.MONTH(date);返回date的月份 1—–12

mysql> select MONTH('2013-06-09 14:00:00');
+------------------------------+
| MONTH('2013-06-09 14:00:00') |
+------------------------------+
| 6 |
+------------------------------+
6.DAYNAME(date);返回date的星期英文名

mysql> select DAYNAME('2013-06-09 14:00:00');
+--------------------------------+
| DAYNAME('2013-06-09 14:00:00') |
+--------------------------------+
| Sunday|
+--------------------------------+
7.MONTHNAME(date);返回date的月份的英文名

mysql> select MONTHNAME('2013-06-09 14:00:00');
+----------------------------------+
| MONTHNAME('2013-06-09 14:00:00') |
+----------------------------------+
| June |
+----------------------------------+
8.QUARTER(date);返回date在季度中的排序 1——-4

mysql> select QUARTER('2013-06-09 14:00:00');
+--------------------------------+
| QUARTER('2013-06-09 14:00:00') |
+--------------------------------+
|2 |
+--------------------------------+
9.WEEK(date,first);返回date是一年的第几个周,first = 0从星期日开始算，first = 1从星期一开始算默认为00——–52

mysql> select WEEK('2013-06-09 14:00:00',0);
+-------------------------------+
| WEEK('2013-06-09 14:00:00',0) |
+-------------------------------+
| 23 |
+-------------------------------+
10.HOUR(date);返回date的小时部分

mysql> select HOUR('2013-06-09 14:00:00');
+-----------------------------+
| HOUR('2013-06-09 14:00:00') |
+-----------------------------+
| 14 |
+-----------------------------+
11.YEAR(date);返回date的年份部分

mysql> select YEAR('2013-06-09 14:00:00');
+-----------------------------+
| YEAR('2013-06-09 14:00:00') |
+-----------------------------+
| 2013 |
+-----------------------------+
12.MINUTE(date);返回date的分钟部分

mysql> select MINUTE('2013-06-09 14:22:22');
+-------------------------------+
| MINUTE('2013-06-09 14:22:22') |
+-------------------------------+
| 22 |
+-------------------------------+
13.SECOND(date);返回date的秒部分

mysql> select SECOND('2013-06-09 14:22:22');
+-------------------------------+
| SECOND('2013-06-09 14:22:22') |
+-------------------------------+
| 22 |
+-------------------------------+
14.PERIOD_ADD(date,num);date加上num后的日期 date的日期格式为 yyyymmdd 或者yyyymm，若精确到日num单位是日，若是月则num为月单位增加

mysql> select PERIOD_ADD(201306,3);
+----------------------+
| PERIOD_ADD(201306,3) |
+----------------------+
|201309 |
+----------------------+
mysql> select PERIOD_ADD(20130609,3);
+------------------------+
| PERIOD_ADD(20130609,3) |
+------------------------+
|20130612 |
+------------------------+
15.PERIOD_DIFF(date1,date2);date1减去date2的差值月数

mysql> select PERIOD_DIFF(201306,201309);
+----------------------------+
| PERIOD_DIFF(201306,201309) |
+----------------------------+
|-3 |
+----------------------------+
16.DATE_ADD(date, INTERVAL num type) == ADDDATE(date, INTERVAL num type);DATE_SUB(date, INTERVAL num type) == SUBDATE(date, INTERVAL num type)；date相加或者相减一个制定的单位时间

type的值: SECOND,MINUTE,HOUR,DAY,MONTH,WEEK,MONTH,YEAR

mysql> select DATE_ADD('2013-06-09 14:22:22',INTERVAL 1 DAY);
+------------------------------------------------+
| DATE_ADD('2013-06-09 14:22:22',INTERVAL 1 DAY) |
+------------------------------------------------+
| 2013-06-10 14:22:22 |
+------------------------------------------------+
mysql> select ADDDATE('2013-06-09 14:22:22',INTERVAL 1 DAY);
+-----------------------------------------------+
| ADDDATE('2013-06-09 14:22:22',INTERVAL 1 DAY) |
+-----------------------------------------------+
| 2013-06-10 14:22:22 |
+-----------------------------------------------+
16.TO_DAYS(date);给定一个日期返回一个天数（从0年开始的天数）

mysql> select TO_DAYS('2013-06-09 14:22:22');
+--------------------------------+
| TO_DAYS('2013-06-09 14:22:22') |
+--------------------------------+
|735393 |
+--------------------------------+
17.FROM_DAYS(num);给定一个天数返回一个日期

mysql> select FROM_DAYS(752341);
+-------------------+
| FROM_DAYS(752341) |
+-------------------+
| 2059-11-03 |
+-------------------+
18.DATE_FORMAT(date,format);格式化日期

%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('2013-06-09 14:22:22','%Y-%m-%d');
+-----------------------------------------------+
| DATE_FORMAT('2013-06-09 14:22:22','%Y-%m-%d') |
+-----------------------------------------------+
| 2013-06-09 |
+-----------------------------------------------+
19.CURDATE() == CURRENT_DATE();分为数字性返回和字符串返回

mysql> select CURDATE();
+------------+
| CURDATE() |
+------------+
| 2013-08-22 |
+------------+
mysql> select CURDATE() + 0;
+---------------+
| CURDATE() + 0 |
+---------------+
| 20130822 |
+---------------+
mysql> select CURDATE() + 1;
+---------------+
| CURDATE() + 1 |
+---------------+
| 20130823 |
+---------------+
20.CURTIME() == CURRENT_TIME();分为数字性返回和字符串返回

mysql> select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 14:08:37 |
+-----------+
mysql> select CURTIME() + 0.;
+----------------+
| CURTIME() + 0. |
+----------------+
| 140841.000000 |
+----------------+
21.NOW(),UNIX_TIMESTAMP();现在时刻的时间和时间戳

mysql> SELECT NOW();
+---------------------+
| NOW()|
+---------------------+
| 2013-08-22 14:13:59 |
+---------------------+
mysql> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
| 1377152057 |
+------------------+
22.FROM_UNIXTIME(unix);将unix时间戳转为日期

mysql> select FROM_UNIXTIME(1377152057);
+---------------------------+
| FROM_UNIXTIME(1377152057) |
+---------------------------+
| 2013-08-22 14:14:17 |
+---------------------------+

补充

--返回当前时间
mysql> select curdate(),curtime(),now(),DATE(now()),sysdate();
+------------+-----------+---------------------+-------------+---------------------+
| curdate() | curtime() | now()               | DATE(now()) | sysdate()           |
+------------+-----------+---------------------+-------------+---------------------+
| 2008-12-02 | 10:11:36 | 2008-12-02 10:11:36 | 2008-12-02 | 2008-12-02 10:11:36 |
+------------+-----------+---------------------+-------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT CURDATE(),CURDATE()+0,CURTIME(),CURTIME()+0;
+------------+-------------+-----------+---------------+
| CURDATE() | CURDATE()+0 | CURTIME() | CURTIME()+0   |
+------------+-------------+-----------+---------------+
| 2008-12-02 |    20081202 | 10:00:33 | 100033.000000 |
+------------+-------------+-----------+---------------+
1 row in set (0.00 sec)
--返回日期当月最后一天
mysql> select last_day('2008-12-02');
+------------------------+
| last_day('2008-12-02') |
+------------------------+
| 2008-12-31             |
+------------------------+
1 row in set (0.00 sec)
--返回日期的星期几
mysql> select dayname('2008-12-02'),dayofweek('2008-12-02');
+-----------------------+-------------------------+
| dayname('2008-12-02') | dayofweek('2008-12-02') |
+-----------------------+-------------------------+
| Tuesday               |                       3 |
+-----------------------+-------------------------+
1 row in set (0.00 sec)
--返回日期的年，月，日
mysql> select month('2008-12-02'),year('2008-12-02'),day('2008-12-02');
+---------------------+--------------------+-------------------+
| month('2008-12-02') | year('2008-12-02') | day('2008-12-02') |
+---------------------+--------------------+-------------------+
|                  12 |               2008 |                 2 |
+---------------------+--------------------+-------------------+
1 row in set (0.00 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)
1.SUBDATE(d,t)：起始时间加上一段时间
--返回起始时间加上N天
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY),ADDDATE('1998-01-02', 31);
+-----------------------------------------+---------------------------+
| DATE_ADD('1998-01-02', INTERVAL 31 DAY) | ADDDATE('1998-01-02', 31) |
+-----------------------------------------+---------------------------+
| 1998-02-02                              | 1998-02-02                |
+-----------------------------------------+---------------------------+
1 row in set (0.00 sec)
--返回起始时间加上年，月
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 2 YEAR), DATE_ADD('1998-01-02', INTERVAL 2 MONTH);
+-----------------------------------------+------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 2 YEAR) | DATE_ADD('1998-01-02', INTERVAL 2 MONTH) |
+-----------------------------------------+------------------------------------------+
| 2000-01-02                              | 1998-03-02                               |
+-----------------------------------------+------------------------------------------+
1 row in set (0.00 sec)
--返回起始时间加上小时，加上分钟
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 2 hour), DATE_ADD('1998-01-02', INTERVAL 2 minute);
+-----------------------------------------+-------------------------------------------+
| DATE_ADD('1998-01-02', INTERVAL 2 hour) | DATE_ADD('1998-01-02', INTERVAL 2 minute) |
+-----------------------------------------+-------------------------------------------+
| 1998-01-02 02:00:00                     | 1998-01-02 00:02:00                       |
+-----------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)
2.SUBDATE(d,t)：起始时间减去一段时间
mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY),SUBDATE('1998-01-02', 31);
+----------------------------------------+---------------------------+
| SUBDATE('1998-01-02', INTERVAL 31 DAY) | SUBDATE('1998-01-02', 31) |
+----------------------------------------+---------------------------+
| 1997-12-02                             | 1997-12-02                |
+----------------------------------------+---------------------------+
1 row in set (0.00 sec)
3.ADDTIME(d,t):起始时间d加入时间t
mysql> SELECT ADDTIME('1997-12-31 23:59:50','00:00:05'), ADDTIME('23:59:50','00:00:05') ;
+-------------------------------------------+--------------------------------+
| ADDTIME('1997-12-31 23:59:50','00:00:05') | ADDTIME('23:59:50','00:00:05') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:55                       | 23:59:55                       |
+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)
4.SUBTIME(d,t):起始时间d减去时间t
mysql> SELECT SUBTIME('1997-12-31 23:59:50','00:00:05'), SUBTIME('23:59:50','00:00:05');
+-------------------------------------------+--------------------------------+
| SUBTIME('1997-12-31 23:59:50','00:00:05') | SUBTIME('23:59:50','00:00:05') |
+-------------------------------------------+--------------------------------+
| 1997-12-31 23:59:45                       | 23:59:45                       |
+-------------------------------------------+--------------------------------+
1 row in set (0.00 sec)
5.DATEDIFF(d1,d2):返回起始时间d1和结束时间d2之间的天数
mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');
+----------------------------------------------+
| DATEDIFF('1997-12-31 23:59:59','1997-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.00 sec)
6.DATE_FORMAT(date,format):根据format字符串显示date值的格式
mysql> SELECT DATE_FORMAT('2008-12-02 22:23:00', '%Y %m %m %H:%i:%s');
+---------------------------------------------------------+
| DATE_FORMAT('2008-12-02 22:23:00', '%Y %m %m %H:%i:%s') |
+---------------------------------------------------------+
| 2008 12 12 22:23:00                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)
7.STR_TO_DATE(str,format) 字符串转化为时间
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y %H:%i:s');
+-----------------------------------------------+
| STR_TO_DATE('04/31/2004', '%m/%d/%Y %H:%i:s') |
+-----------------------------------------------+
| 2004-04-31 00:00:00                           |
+-----------------------------------------------+
1 row in set (0.00 sec)
8.TIMESTAMP(expr) , TIMESTAMP(expr,expr2) :
对于一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回.对于两个参数, 它将时间表达式 expr2添加到日期或日期时间表达式 expr 中，将theresult作为日期时间值返回
mysql> SELECT TIMESTAMP('2003-12-31'), TIMESTAMP('2003-12-31 12:00:00','12:00:00');
+-------------------------+---------------------------------------------+
| TIMESTAMP('2003-12-31') | TIMESTAMP('2003-12-31 12:00:00','12:00:00') |
+-------------------------+---------------------------------------------+
| 2003-12-31 00:00:00     | 2004-01-01 00:00:00                         |
+-------------------------+---------------------------------------------+
1 row in set (0.00 sec)

--取当天0点0分，下一天0点0分
mysql> select timestamp(date(sysdate())),timestamp(adddate(date(sysdate()),1));
+----------------------------+---------------------------------------+
| timestamp(date(sysdate())) | timestamp(adddate(date(sysdate()),1)) |
+----------------------------+---------------------------------------+
| 2008-12-02 00:00:00 | 2008-12-03 00:00:00 |
+----------------------------+---------------------------------------+
1 row in set (0.00 sec)

打赏