MySQL中的常用日期和时间函数

2020年2月26日
| 没有评论
摘要：MySQL中的常用日期和时间函数介绍及实测。

日期和时间函数与字符串函数、数值函数是使用频率最高的三大类函数，今天我们就来看看它。下表是MySQL 5.7中支持的日期和时间函数。

Name Description
ADDDATE() Add time values (intervals) to a date value
ADDTIME() Add time
CONVERT_TZ() Convert from one time zone to another
CURDATE() Return the current date
CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()
CURTIME() Return the current time
DATE() Extract the date part of a date or datetime expression
DATE_ADD() Add time values (intervals) to a date value
DATE_FORMAT() Format date as specified
DATE_SUB() Subtract a time value (interval) from a date
DATEDIFF() Subtract two dates
DAY() Synonym for DAYOFMONTH()
DAYNAME() Return the name of the weekday
DAYOFMONTH() Return the day of the month (0-31)
DAYOFWEEK() Return the weekday index of the argument
DAYOFYEAR() Return the day of the year (1-366)
EXTRACT() Extract part of a date
FROM_DAYS() Convert a day number to a date
FROM_UNIXTIME() Format Unix timestamp as a date
GET_FORMAT() Return a date format string
HOUR() Extract the hour
LAST_DAY Return the last day of the month for the argument
LOCALTIME(), LOCALTIME Synonym for NOW()
LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
MAKEDATE() Create a date from the year and day of year
MAKETIME() Create time from hour, minute, second
MICROSECOND() Return the microseconds from argument
MINUTE() Return the minute from the argument
MONTH() Return the month from the date passed
MONTHNAME() Return the name of the month
NOW() Return the current date and time
PERIOD_ADD() Add a period to a year-month
PERIOD_DIFF() Return the number of months between periods
QUARTER() Return the quarter from a date argument
SEC_TO_TIME() Converts seconds to ‘hh:mm:ss’ format
SECOND() Return the second (0-59)
STR_TO_DATE() Convert a string to a date
SUBDATE() Synonym for DATE_SUB() when invoked with three arguments
SUBTIME() Subtract times
SYSDATE() Return the time at which the function executes
TIME() Extract the time portion of the expression passed
TIME_FORMAT() Format as time
TIME_TO_SEC() Return the argument converted to seconds
TIMEDIFF() Subtract time
TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD() Add an interval to a datetime expression
TIMESTAMPDIFF() Subtract an interval from a datetime expression
TO_DAYS() Return the date argument converted to days
TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP() Return a Unix timestamp
UTC_DATE() Return the current UTC date
UTC_TIME() Return the current UTC time
UTC_TIMESTAMP() Return the current UTC date and time
WEEK() Return the week number
WEEKDAY() Return the weekday index
WEEKOFYEAR() Return the calendar week of the date (1-53)
YEAR() Return the year
YEARWEEK() Return the year and week

我们通过实例来研究下常用函数的用法。

  • CURDATE()，返回当前日期，只包含年月日
root@database-one 12:34:  [gftest]> SELECT CURDATE(),CURDATE()+0,CURDATE()+1,CURDATE()+2;
+------------+-------------+-------------+-------------+
| CURDATE()  | CURDATE()+0 | CURDATE()+1 | CURDATE()+2 |
+------------+-------------+-------------+-------------+
| 2020-02-23 |    20200223 |    20200224 |    20200225 |
+------------+-------------+-------------+-------------+
1 row in set (0.10 sec)

可以看到，CURDATE()的返回值格式是’YYYY-MM-DD’ 或 YYYYMMDD，取决于你要进行的后续操作类型。

  • CURTIME([fsp])，返回当前时间，只包含时分秒
    可选参数fsp，指定从0到6的小数秒精度。
root@database-one 12:40:  [gftest]> select curtime(),curtime()+0,curtime(2)+1,curtime(6)+2;
+-----------+-------------+--------------+---------------+
| curtime() | curtime()+0 | curtime(2)+1 | curtime(6)+2  |
+-----------+-------------+--------------+---------------+
| 12:41:43  |      124143 |    124144.93 | 124145.938067 |
+-----------+-------------+--------------+---------------+
1 row in set (0.05 sec)
  • NOW([fsp])，返回当前的日期和时间，年月日时分秒全包含
    可选参数fsp，指定从0到6的小数秒精度。
root@database-one 12:47:  [gftest]> select now(),now()+0,now(2)+1,now(6)+2;
+---------------------+----------------+-------------------+-----------------------+
| now()               | now()+0        | now(2)+1          | now(6)+2              |
+---------------------+----------------+-------------------+-----------------------+
| 2020-02-23 12:48:21 | 20200223124821 | 20200223124822.55 | 20200223124823.551110 |
+---------------------+----------------+-------------------+-----------------------+
1 row in set (0.02 sec)

MySQL也支持SYSDATE()，但它和NOW()是有区别的，看个列子：

root@database-one 12:53:  [gftest]> select now(),sleep(2),now();
+---------------------+----------+---------------------+
| now()               | sleep(2) | now()               |
+---------------------+----------+---------------------+
| 2020-02-23 12:53:44 |        0 | 2020-02-23 12:53:44 |
+---------------------+----------+---------------------+
1 row in set (2.03 sec)

root@database-one 12:53:  [gftest]> select sysdate(),sleep(2),sysdate();
+---------------------+----------+---------------------+
| sysdate()           | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+
| 2020-02-23 12:54:01 |        0 | 2020-02-23 12:54:03 |
+---------------------+----------+---------------------+
1 row in set (2.05 sec)

root@database-one 12:54:  [gftest]> select now(),sleep(2),sysdate();
+---------------------+----------+---------------------+
| now()               | sleep(2) | sysdate()           |
+---------------------+----------+---------------------+
| 2020-02-23 12:55:30 |        0 | 2020-02-23 12:55:32 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

root@database-one 12:55:  [gftest]> select sysdate(),sleep(2),now();
+---------------------+----------+---------------------+
| sysdate()           | sleep(2) | now()               |
+---------------------+----------+---------------------+
| 2020-02-23 12:55:44 |        0 | 2020-02-23 12:55:44 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)

可以看到，NOW()返回语句开始执行的时间（在函数或触发器中，NOW()返回函数或触发器语句开始执行）。SYSDATE()返回它自己被执行的时间。

  • UNIX_TIMESTAMP([date])，返回日期date的UNIX时间戳

这个时间戳怎么理解呢？如果调用UNIX_TIMESTAMP()时没有日期参数，则返回从UTC时间’1970-01-01 00:00:00’开始到当前时间的秒数。如果用日期参数调用UNIX_TIMESTAMP()，则返回从UTC时间’1970-01-01 00:00:00’开始到参数的秒数。
[date]参数可以是DATE、DATETIME或TIMESTAMP字符串，也可以是YYMMDD、YYMMDDHHMMSS、YYYYMMDDDDDD或YYYYMMDDHHMMSS格式的数字。如果参数包含时间部分，则可以选择包含小数秒部分。

root@database-one 13:12:  [gftest]> SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2015-11-13 10:20:19'),UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
+------------------+---------------------------------------+-------------------------------------------+
| UNIX_TIMESTAMP() | UNIX_TIMESTAMP('2015-11-13 10:20:19') | UNIX_TIMESTAMP('2015-11-13 10:20:19.012') |
+------------------+---------------------------------------+-------------------------------------------+
|       1582434736 |                            1447381219 |                            1447381219.012 |
+------------------+---------------------------------------+-------------------------------------------+
1 row in set (0.02 sec)
  • FROM_UNIXTIME(unix_timestamp[,format])，返回时间戳参数的日期值，和UNIX_TIMESTAMP()互为逆操作
root@database-one 13:14:  [gftest]> select from_unixtime(1582434736),from_unixtime(1447381219),from_unixtime(1447381219.012);
+---------------------------+---------------------------+-------------------------------+
| from_unixtime(1582434736) | from_unixtime(1447381219) | from_unixtime(1447381219.012) |
+---------------------------+---------------------------+-------------------------------+
| 2020-02-23 13:12:16       | 2015-11-13 10:20:19       | 2015-11-13 10:20:19.012       |
+---------------------------+---------------------------+-------------------------------+
1 row in set (0.04 sec)
  • YEAR(date)，MONTH(date)，DAY(date)，HOUR(time)，MINUTE(time)，SECOND(time)，分别返回时间参数的年、月、日、时、分、秒各部分
root@database-one 13:28:  [gftest]> select now(),YEAR(now()),MONTH(now()),DAY(now()),HOUR(now()),MINUTE(now()),SECOND(now());
+---------------------+-------------+--------------+------------+-------------+---------------+---------------+
| now()               | YEAR(now()) | MONTH(now()) | DAY(now()) | HOUR(now()) | MINUTE(now()) | SECOND(now()) |
+---------------------+-------------+--------------+------------+-------------+---------------+---------------+
| 2020-02-23 13:28:17 |        2020 |            2 |         23 |          13 |            28 |            17 |
+---------------------+-------------+--------------+------------+-------------+---------------+---------------+
1 row in set (0.06 sec)
  • MONTHNAME(date)，返回参数的英文月份名称
root@database-one 13:29:  [gftest]> select now(),monthname(now());
+---------------------+------------------+
| now()               | monthname(now()) |
+---------------------+------------------+
| 2020-02-23 13:30:05 | February         |
+---------------------+------------------+
1 row in set (0.10 sec)
  • DATE_FORMAT(date,format)，按字符串format格式化日期date值

MySQL5.7中可以用到的格式符如下表：

Specifier Description
%a Abbreviated weekday name (Sun…Sat)
%b Abbreviated month name (Jan…Dec)
%c Month, numeric (0…12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00…31)
%e Day of the month, numeric (0…31)
%f Microseconds (000000…999999)
%H Hour (00…23)
%h和%I Hour (01…12)
%i Minutes, numeric (00…59)
%j Day of year (001…366)
%k Hour (0…23)
%l Hour (1…12)
%M Month name (January…December)
%m Month, numeric (00…12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S和%s Seconds (00…59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00…53), where Sunday is the first day of the week; WEEK() mode 0
%u Week (00…53), where Monday is the first day of the week; WEEK() mode 1
%V Week (01…53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%v Week (01…53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%W Weekday name (Sunday…Saturday)
%w Day of the week (0=Sunday…6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal % character
%x x, for any “x” not listed above 
root@database-one 13:52:  [gftest]> select now(),date_format(now(),'%M,%D,%Y');
+---------------------+-------------------------------+
| now()               | date_format(now(),'%M,%D,%Y') |
+---------------------+-------------------------------+
| 2020-02-23 13:52:48 | February,23rd,2020            |
+---------------------+-------------------------------+
1 row in set (0.05 sec)

root@database-one 13:52:  [gftest]> SELECT DATE_FORMAT('2006-06-00', '%d');
+---------------------------------+
| DATE_FORMAT('2006-06-00', '%d') |
+---------------------------------+
| 00                              |
+---------------------------------+
1 row in set (0.08 sec)

 

  • DATE_ADD(date,INTERVAL expr unit)，返回与所给日期相差INTERVAL时段的日期
    INTERVAL是关键字，unit是真正的间隔类型，在MySQL5.7中提供了20种间隔类型（详见下表），expr是按照间隔类型格式书写的表达式。
unit Value Expected expr Format
MICROSECOND MICROSECONDS
SECOND SECONDS
MINUTE MINUTES
HOUR HOURS
DAY DAYS
WEEK WEEKS
MONTH MONTHS
QUARTER QUARTERS
YEAR YEARS
SECOND_MICROSECOND ‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND ‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND ‘MINUTES:SECONDS’
HOUR_MICROSECOND ‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND ‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE ‘HOURS:MINUTES’
DAY_MICROSECOND ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND ‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE ‘DAYS HOURS:MINUTES’
DAY_HOUR ‘DAYS HOURS’
YEAR_MONTH ‘YEARS-MONTHS’ 






root@database-one 14:11:  [gftest]> select now(),date_add(now(),interval 20 day),date_add(now(),interval 3 month),date_add(now(),interval '1_2' year_month);
+---------------------+---------------------------------+----------------------------------+-------------------------------------------+
| now()               | date_add(now(),interval 20 day) | date_add(now(),interval 3 month) | date_add(now(),interval '1_2' year_month) |
+---------------------+---------------------------------+----------------------------------+-------------------------------------------+
| 2020-02-23 14:12:03 | 2020-03-14 14:12:03             | 2020-05-23 14:12:03              | 2021-04-23 14:12:03                       |
+---------------------+---------------------------------+----------------------------------+-------------------------------------------+
1 row in set (0.00 sec)


 




也可以使用负数返回过去的某个日期时间。




root@database-one 14:14:  [gftest]> select now(),date_add(now(),interval -20 day),date_add(now(),interval -3 month),date_add(now(),interval '-1_-2' year_month);
+---------------------+----------------------------------+-----------------------------------+---------------------------------------------+
| now()               | date_add(now(),interval -20 day) | date_add(now(),interval -3 month) | date_add(now(),interval '-1_-2' year_month) |
+---------------------+----------------------------------+-----------------------------------+---------------------------------------------+
| 2020-02-23 14:15:22 | 2020-02-03 14:15:22              | 2019-11-23 14:15:22               | 2018-12-23 14:15:22                         |
+---------------------+----------------------------------+-----------------------------------+---------------------------------------------+
1 row in set (0.01 sec)


 




    

  • DATEDIFF(expr1,expr2)，返回两个日期之间相差的天数
    • 





root@database-one 14:17:  [gftest]> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
+----------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30') |
+----------------------------------------------+
|                                            1 |
+----------------------------------------------+
1 row in set (0.05 sec)

root@database-one 14:17:  [gftest]> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30 12:08:03');
+-------------------------------------------------------+
| DATEDIFF('2007-12-31 23:59:59','2007-12-30 12:08:03') |
+-------------------------------------------------------+
|                                                     1 |
+-------------------------------------------------------+
1 row in set (0.04 sec)


 




从上面可以看到，只有参数的日期部分参与计算。




© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。

                

            
MySQL

            
 | Tags: , , , , , , ,