夏日博客

A-A+

mysql时间日期函数例子

2017年10月16日 PHP技术文章 暂无评论 阅读 0 views 次

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

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

  1. mysql> select DAYOFWEEK('2013-06-09 14:00:00');   
  2. +----------------------------------+   
  3. | DAYOFWEEK('2013-06-09 14:00:00') |   
  4. +----------------------------------+   
  5. |  1 |   
  6. +----------------------------------+   

2.DAYOFMONTH(date);返回date是一个月的第几天 1——31

  1. mysql> select DAYOFMONTH('2013-06-09 14:00:00');   
  2. +-----------------------------------+   
  3. | DAYOFMONTH('2013-06-09 14:00:00') |   
  4. +-----------------------------------+   
  5. |   9 |   
  6. +-----------------------------------+   

3.DAYOFYEAR(date);返回date是一年中的第几天 1———366

  1. mysql> select DAYOFYEAR('2013-06-09 14:00:00');   
  2. +----------------------------------+   
  3. | DAYOFYEAR('2013-06-09 14:00:00') |   
  4. +----------------------------------+   
  5. |160 |   
  6. +----------------------------------+   

4.WEEKDAY(date);返回date是周几(0 => 星期一，1 => 星期二，2 => 星期三，……, 6=> 星期日).

  1. mysql> select WEEKDAY('2013-06-09 14:00:00');   
  2. +--------------------------------+   
  3. | WEEKDAY('2013-06-09 14:00:00') |   
  4. +--------------------------------+   
  5. |6 |   
  6. +--------------------------------+   

5.MONTH(date);返回date的月份 1—–12

  1. mysql> select MONTH('2013-06-09 14:00:00');   
  2. +------------------------------+   
  3. | MONTH('2013-06-09 14:00:00') |   
  4. +------------------------------+   
  5. |   6 |   
  6. +------------------------------+   

6.DAYNAME(date);返回date的星期英文名

  1. mysql> select DAYNAME('2013-06-09 14:00:00');   
  2. +--------------------------------+   
  3. | DAYNAME('2013-06-09 14:00:00') |   
  4. +--------------------------------+   
  5. | Sunday|   
  6. +--------------------------------+   

7.MONTHNAME(date);返回date的月份的英文名

  1. mysql> select MONTHNAME('2013-06-09 14:00:00');   
  2. +----------------------------------+   
  3. | MONTHNAME('2013-06-09 14:00:00') |   
  4. +----------------------------------+   
  5. | June    |   
  6. +----------------------------------+   

8.QUARTER(date);返回date在季度中的排序 1——-4

  1. mysql> select QUARTER('2013-06-09 14:00:00');   
  2. +--------------------------------+   
  3. | QUARTER('2013-06-09 14:00:00') |   
  4. +--------------------------------+   
  5. |2 |   
  6. +--------------------------------+   

9.WEEK(date,first);返回date是一年的第几个周,first = 0从星期日开始算，first = 1从星期一开始算 默认为00——–52

  1. mysql> select WEEK('2013-06-09 14:00:00',0);   
  2. +-------------------------------+   
  3. | WEEK('2013-06-09 14:00:00',0) |   
  4. +-------------------------------+   
  5. |   23 |   
  6. +-------------------------------+   

10.HOUR(date);返回date的小时部分

  1. mysql> select HOUR('2013-06-09 14:00:00');   
  2. +-----------------------------+   
  3. | HOUR('2013-06-09 14:00:00') |   
  4. +-----------------------------+   
  5. | 14 |   
  6. +-----------------------------+   

11.YEAR(date);返回date的年份部分

  1. mysql> select YEAR('2013-06-09 14:00:00');   
  2. +-----------------------------+   
  3. | YEAR('2013-06-09 14:00:00') |   
  4. +-----------------------------+   
  5. |    2013 |   
  6. +-----------------------------+   

12.MINUTE(date);返回date的分钟部分

  1. mysql> select MINUTE('2013-06-09 14:22:22');   
  2. +-------------------------------+   
  3. | MINUTE('2013-06-09 14:22:22') |   
  4. +-------------------------------+   
  5. |   22 |   
  6. +-------------------------------+   

13.SECOND(date);返回date的秒部分

  1. mysql> select SECOND('2013-06-09 14:22:22');   
  2. +-------------------------------+   
  3. | SECOND('2013-06-09 14:22:22') |   
  4. +-------------------------------+   
  5. |   22 |   
  6. +-------------------------------+   

14.PERIOD_ADD(date,num);date加上num后的日期 date的日期格式为 yyyymmdd 或者yyyymm，若精确到日num单位是日，若是月则num为月单位增加

  1. mysql> select PERIOD_ADD(201306,3);   
  2. +----------------------+   
  3. | PERIOD_ADD(201306,3) |   
  4. +----------------------+   
  5. |201309 |   
  6. +----------------------+   
  7. mysql> select PERIOD_ADD(20130609,3);   
  8. +------------------------+   
  9. | PERIOD_ADD(20130609,3) |   
  10. +------------------------+   
  11. |20130612 |   
  12. +------------------------+   

15.PERIOD_DIFF(date1,date2);date1减去date2的差值月数

  1. mysql> select PERIOD_DIFF(201306,201309);   
  2. +----------------------------+   
  3. | PERIOD_DIFF(201306,201309) |   
  4. +----------------------------+   
  5. |-3 |   
  6. +----------------------------+   

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

  1. mysql> select DATE_ADD('2013-06-09 14:22:22',INTERVAL 1 DAY);   
  2. +------------------------------------------------+   
  3. | DATE_ADD('2013-06-09 14:22:22',INTERVAL 1 DAY) |   
  4. +------------------------------------------------+   
  5. | 2013-06-10 14:22:22   |   
  6. +------------------------------------------------+   
  7. mysql> select ADDDATE('2013-06-09 14:22:22',INTERVAL 1 DAY);   
  8. +-----------------------------------------------+   
  9. | ADDDATE('2013-06-09 14:22:22',INTERVAL 1 DAY) |   
  10. +-----------------------------------------------+   
  11. | 2013-06-10 14:22:22  |   
  12. +-----------------------------------------------+   

16.TO_DAYS(date);给定一个日期返回一个天数（从0年开始的天数）

  1. mysql> select TO_DAYS('2013-06-09 14:22:22');   
  2. +--------------------------------+   
  3. | TO_DAYS('2013-06-09 14:22:22') |   
  4. +--------------------------------+   
  5. |735393 |   
  6. +--------------------------------+   

17.FROM_DAYS(num);给定一个天数 返回一个日期

  1. mysql> select FROM_DAYS(752341);   
  2. +-------------------+   
  3. | FROM_DAYS(752341) |   
  4. +-------------------+   
  5. | 2059-11-03   |   
  6. +-------------------+   

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), 这里星期一是星期的第一天

%% 一个文字“%”。

  1. mysql> select DATE_FORMAT('2013-06-09 14:22:22','%Y-%m-%d');   
  2. +-----------------------------------------------+   
  3. | DATE_FORMAT('2013-06-09 14:22:22','%Y-%m-%d') |   
  4. +-----------------------------------------------+   
  5. | 2013-06-09 |   
  6. +-----------------------------------------------+   

19.CURDATE() == CURRENT_DATE();分为数字性返回和字符串返回

  1. mysql> select CURDATE();   
  2. +------------+   
  3. | CURDATE()  |   
  4. +------------+   
  5. | 2013-08-22 |   
  6. +------------+   
  7. mysql> select CURDATE() + 0;   
  8. +---------------+   
  9. | CURDATE() + 0 |   
  10. +---------------+   
  11. | 20130822 |   
  12. +---------------+   
  13. mysql> select CURDATE() + 1;   
  14. +---------------+   
  15. | CURDATE() + 1 |   
  16. +---------------+   
  17. | 20130823 |   
  18. +---------------+   

20.CURTIME() == CURRENT_TIME();分为数字性返回和字符串返回

  1. mysql> select CURTIME();   
  2. +-----------+   
  3. | CURTIME() |   
  4. +-----------+   
  5. | 14:08:37  |   
  6. +-----------+   
  7. mysql> select CURTIME() + 0.;   
  8. +----------------+   
  9. | CURTIME() + 0. |   
  10. +----------------+   
  11. |  140841.000000 |   
  12. +----------------+   

21.NOW(),UNIX_TIMESTAMP();现在时刻的时间和时间戳

  1. mysql> SELECT NOW();   
  2. +---------------------+   
  3. | NOW()|   
  4. +---------------------+   
  5. | 2013-08-22 14:13:59 |   
  6. +---------------------+   
  7. mysql> SELECT UNIX_TIMESTAMP();   
  8. +------------------+   
  9. | UNIX_TIMESTAMP() |   
  10. +------------------+   
  11. |  1377152057 |   
  12. +------------------+   

22.FROM_UNIXTIME(unix);将unix时间戳转为日期

  1. mysql> select FROM_UNIXTIME(1377152057);   
  2. +---------------------------+   
  3. | FROM_UNIXTIME(1377152057) |   
  4. +---------------------------+   
  5. | 2013-08-22 14:14:17  |   
  6. +---------------------------+   

补充:--返回当前时间

  1. mysql> select curdate(),curtime(),now(),DATE(now()),sysdate();   
  2. +------------+-----------+---------------------+-------------+---------------------+   
  3. | curdate()  | curtime() | now()               | DATE(now()) | sysdate()           |   
  4. +------------+-----------+---------------------+-------------+---------------------+   
  5. | 2008-12-02 | 10:11:36  | 2008-12-02 10:11:36 | 2008-12-02  | 2008-12-02 10:11:36 |   
  6. +------------+-----------+---------------------+-------------+---------------------+   
  7. 1 row in set (0.00 sec)   
  8. mysql> SELECT CURDATE(),CURDATE()+0,CURTIME(),CURTIME()+0;           
  9. +------------+-------------+-----------+---------------+   
  10. | CURDATE()  | CURDATE()+0 | CURTIME() | CURTIME()+0   |   
  11. +------------+-------------+-----------+---------------+   
  12. | 2008-12-02 |    20081202 | 10:00:33  | 100033.000000 |   
  13. +------------+-------------+-----------+---------------+   
  14. 1 row in set (0.00 sec)   
  15. --返回日期当月最后一天   
  16. mysql> select last_day('2008-12-02');   
  17. +------------------------+   
  18. | last_day('2008-12-02') |   
  19. +------------------------+   
  20. | 2008-12-31             |   
  21. +------------------------+   
  22. 1 row in set (0.00 sec)   
  23. --返回日期的星期几   
  24. mysql> select dayname('2008-12-02'),dayofweek('2008-12-02');   
  25. +-----------------------+-------------------------+   
  26. | dayname('2008-12-02') | dayofweek('2008-12-02') |   
  27. +-----------------------+-------------------------+   
  28. | Tuesday               |                       3 |   
  29. +-----------------------+-------------------------+   
  30. 1 row in set (0.00 sec)   
  31. --返回日期的年，月，日   
  32. mysql> select month('2008-12-02'),year('2008-12-02'),day('2008-12-02');   
  33. +---------------------+--------------------+-------------------+   
  34. | month('2008-12-02') | year('2008-12-02') | day('2008-12-02') |   
  35. +---------------------+--------------------+-------------------+   
  36. |                  12 |               2008 |                 2 |   
  37. +---------------------+--------------------+-------------------+   
  38. 1 row in set (0.00 sec)   
  39. --返回日期的小时，分，秒   
  40. mysql> SELECT HOUR('10:05:03'),MINUTE('10:05:03'),SECOND('10:05:03');           
  41. +------------------+--------------------+--------------------+   
  42. | HOUR('10:05:03') | MINUTE('10:05:03') | SECOND('10:05:03') |   
  43. +------------------+--------------------+--------------------+   
  44. |               10 |                  5 |                  3 |   
  45. +------------------+--------------------+--------------------+   
  46. 1 row in set (0.00 sec)   
  47. 1.SUBDATE(d,t)：起始时间加上一段时间   
  48. --返回起始时间加上N天   
  49. mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY),ADDDATE('1998-01-02', 31);          
  50. +-----------------------------------------+---------------------------+   
  51. | DATE_ADD('1998-01-02', INTERVAL 31 DAY) | ADDDATE('1998-01-02', 31) |   
  52. +-----------------------------------------+---------------------------+   
  53. | 1998-02-02                              | 1998-02-02                |   
  54. +-----------------------------------------+---------------------------+   
  55. 1 row in set (0.00 sec)   
  56. --返回起始时间加上年，月    
  57. mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 2 YEAR), DATE_ADD('1998-01-02', INTERVAL 2 MONTH);    
  58. +-----------------------------------------+------------------------------------------+   
  59. | DATE_ADD('1998-01-02', INTERVAL 2 YEAR) | DATE_ADD('1998-01-02', INTERVAL 2 MONTH) |   
  60. +-----------------------------------------+------------------------------------------+   
  61. | 2000-01-02                              | 1998-03-02                               |   
  62. +-----------------------------------------+------------------------------------------+   
  63. 1 row in set (0.00 sec)   
  64. --返回起始时间加上小时，加上分钟   
  65. mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 2 hour), DATE_ADD('1998-01-02', INTERVAL 2 minute);   
  66. +-----------------------------------------+-------------------------------------------+   
  67. | DATE_ADD('1998-01-02', INTERVAL 2 hour) | DATE_ADD('1998-01-02', INTERVAL 2 minute) |   
  68. +-----------------------------------------+-------------------------------------------+   
  69. | 1998-01-02 02:00:00                     | 1998-01-02 00:02:00                       |   
  70. +-----------------------------------------+-------------------------------------------+   
  71. 1 row in set (0.00 sec)   
  72. 2.SUBDATE(d,t)：起始时间减去一段时间   
  73. mysql> SELECT SUBDATE('1998-01-02', INTERVAL 31 DAY),SUBDATE('1998-01-02', 31);   
  74. +----------------------------------------+---------------------------+   
  75. | SUBDATE('1998-01-02', INTERVAL 31 DAY) | SUBDATE('1998-01-02', 31) |   
  76. +----------------------------------------+---------------------------+   
  77. | 1997-12-02                             | 1997-12-02                |   
  78. +----------------------------------------+---------------------------+   
  79. 1 row in set (0.00 sec)   
  80. 3.ADDTIME(d,t):起始时间d加入时间t   
  81. mysql> SELECT ADDTIME('1997-12-31 23:59:50','00:00:05'), ADDTIME('23:59:50','00:00:05') ;   
  82. +-------------------------------------------+--------------------------------+   
  83. | ADDTIME('1997-12-31 23:59:50','00:00:05') | ADDTIME('23:59:50','00:00:05') |   
  84. +-------------------------------------------+--------------------------------+   
  85. | 1997-12-31 23:59:55                       | 23:59:55                       |   
  86. +-------------------------------------------+--------------------------------+   
  87. 1 row in set (0.00 sec)   
  88. 4.SUBTIME(d,t):起始时间d减去时间t   
  89. mysql> SELECT SUBTIME('1997-12-31 23:59:50','00:00:05'), SUBTIME('23:59:50','00:00:05');         
  90. +-------------------------------------------+--------------------------------+   
  91. | SUBTIME('1997-12-31 23:59:50','00:00:05') | SUBTIME('23:59:50','00:00:05') |   
  92. +-------------------------------------------+--------------------------------+   
  93. | 1997-12-31 23:59:45                       | 23:59:45                       |   
  94. +-------------------------------------------+--------------------------------+   
  95. 1 row in set (0.00 sec)   
  96. 5.DATEDIFF(d1,d2):返回起始时间d1和结束时间d2之间的天数   
  97. mysql> SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30');   
  98. +----------------------------------------------+   
  99. | DATEDIFF('1997-12-31 23:59:59','1997-12-30') |   
  100. +----------------------------------------------+   
  101. |                                            1 |   
  102. +----------------------------------------------+   
  103. 1 row in set (0.00 sec)   
  104. 6.DATE_FORMAT(date,format):根据format字符串显示date值的格式   
  105. mysql> SELECT DATE_FORMAT('2008-12-02 22:23:00', '%Y %m %m %H:%i:%s');   
  106. +---------------------------------------------------------+   
  107. | DATE_FORMAT('2008-12-02 22:23:00', '%Y %m %m %H:%i:%s') |   
  108. +---------------------------------------------------------+   
  109. | 2008 12 12 22:23:00                                     |   
  110. +---------------------------------------------------------+   
  111. 1 row in set (0.00 sec)   
  112. 7.STR_TO_DATE(str,format) 字符串转化为时间   
  113. mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y %H:%i:s');   
  114. +-----------------------------------------------+   
  115. | STR_TO_DATE('04/31/2004', '%m/%d/%Y %H:%i:s') |   
  116. +-----------------------------------------------+   
  117. | 2004-04-31 00:00:00                           |   
  118. +-----------------------------------------------+   
  119. 1 row in set (0.00 sec)   
  120. 8.TIMESTAMP(expr) , TIMESTAMP(expr,expr2) :   
  121. 对于一个单参数,该函数将日期或日期时间表达式 expr 作为日期时间值返回.对于两个参数, 它将时间表达式 expr2添加到日期或日期时间表达式 expr  中，将theresult作为日期时间值返回   
  122. mysql> SELECT TIMESTAMP('2003-12-31'), TIMESTAMP('2003-12-31 12:00:00','12:00:00');   
  123. +-------------------------+---------------------------------------------+   
  124. | TIMESTAMP('2003-12-31') | TIMESTAMP('2003-12-31 12:00:00','12:00:00') |   
  125. +-------------------------+---------------------------------------------+   
  126. | 2003-12-31 00:00:00     | 2004-01-01 00:00:00                         |   
  127. +-------------------------+---------------------------------------------+   
  128. 1 row in set (0.00 sec)   
  129. --取当天0点0分，下一天0点0分   
  130. mysql> select timestamp(date(sysdate())),timestamp(adddate(date(sysdate()),1));          
  131. +----------------------------+---------------------------------------+   
  132. | timestamp(date(sysdate())) | timestamp(adddate(date(sysdate()),1)) |   
  133. +----------------------------+---------------------------------------+   
  134. | 2008-12-02 00:00:00        | 2008-12-03 00:00:00                   |   
  135. +----------------------------+---------------------------------------+   
  136. 1 row in set (0.00 sec)   
  137. //开源软件:www.xiariboke.net  
标签：

给我留言