导读
因为之前转载了一篇MySQL日期时间函数的文章,回头再看,发现有点乱,于是想自己写一个,主要参考官方文档。本文主要把MySQL的日期时间相关的函数做一个完整梳理,再自己简要分类组织一下。希望做到易读易查。欢迎对完善文章有益的建议。
另外关于MySQL日期时间函数还有一些需要单独强调的点:
接收日期的函数通常也可以接受日期时间,但是会忽略时间,同样地,接受时间的函数通常也可以接受日期时间然后忽略日期部分。
说到时间日期,绕不开的就是时区了。CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), 还有FROM_UNIXTIME() 这几个函数会根据当前会话(session)的时区来返回相应的结果,另外UNIX_TIMESTAMP() 会把入参当作当前会话的时区下的日期时间。关于Mysql的时区相关内容,参考我的另一篇文章。
一些日期函数允许“零”日期或者称为不完整日期,比如’2021-06-00’,但是有的函数不允许。
通常用于从日期从提取部分内容的函数在遇到这种情况会返回0:
mysql> SELECT YEAR('2021-06-00'),MONTH('2021-00-00'),DAY('2021-06-00');
+--------------------+---------------------+-------------------+
| YEAR('2021-06-00') | MONTH('2021-00-00') | DAY('2021-06-00') |
+--------------------+---------------------+-------------------+
| 2021 | 0 | 0 |
+--------------------+---------------------+-------------------+
1 row in set (0.00 sec)
另外一些希望接收完整日期的函数会返回NULL。包括用于日期计算或者获取名字的函数:
mysql> SELECT DATE_ADD('2021-06-00',INTERVAL 1 DAY),DAYNAME('2021-06-00');
+---------------------------------------+-----------------------+
| DATE_ADD('2021-06-00',INTERVAL 1 DAY) | DAYNAME('2021-06-00') |
+---------------------------------------+-----------------------+
| NULL | NULL |
+---------------------------------------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
比如:CONVERT_TZ(), DATE_ADD(), DATE_SUB(), DAYOFYEAR(), TIMESTAMPDIFF(), TO_DAYS(), TO_SECONDS(), WEEK(), WEEKDAY(), WEEKOFYEAR(), YEARWEEK().
MySQL中TIME, DATETIME, 和 TIMESTAMP都支持小数秒,最多六位小数,即精确到微秒。当入参包含小数秒,返回值也会恰当地包含小数秒。
获取当前时间
获取当前日期和时间
- NOW([fsp])
- CURRENT_TIMESTAMP()
- CURRENT_TIMESTAMP
- LOCALTIME()
- LOCALTIME
- LOCALTIMESTAMP
- LOCALTIMESTAMP()
返回’YYYY-MM-DD hh:mm:ss’ 或者YYYYMMDDhhmmss格式的日期时间,具体返回哪种根据上下文:
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2021-06-23 01:39:29 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT NOW() + 0;
+----------------+
| NOW() + 0 |
+----------------+
| 20210623013936 |
+----------------+
1 row in set (0.00 sec)
fsp参数用于指定秒后面的小数位的位数:
mysql> SELECT NOW(2);
+------------------------+
| NOW(2) |
+------------------------+
| 2021-06-23 01:37:16.72 |
+------------------------+
1 row in set (0.00 sec)
mysql> select NOW(2)+0;
+-------------------+
| NOW(2)+0 |
+-------------------+
| 20210623013727.44 |
+-------------------+
1 row in set (0.01 sec)
这几个都是完全一样的,鉴于此,建议统一使用now();
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2021-06-22 09:42:32 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT LOCALTIME();
+---------------------+
| LOCALTIME() |
+---------------------+
| 2021-06-22 09:46:46 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT LOCALTIME;
+---------------------+
| LOCALTIME |
+---------------------+
| 2021-06-22 09:46:58 |
+---------------------+
1 row in set (0.00 sec)
- SYSDATE([fsp])
和now()几乎一样,不过now()是返回语句开始执行的时间,SYSDATE()返回函数执行时间:
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2021-06-23 01:45:29 | 0 | 2021-06-23 01:45:29 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2021-06-23 01:45:43 | 0 | 2021-06-23 01:45:45 |
+---------------------+----------+---------------------+
1 row in set (2.00 sec)
获取当前日期
- CURDATE()
- CURRENT_DATE()
- CURRENT_DATE
效果相同,获取当前日期:
mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE;
+------------+----------------+--------------+
| CURDATE() | CURRENT_DATE() | CURRENT_DATE |
+------------+----------------+--------------+
| 2021-06-23 | 2021-06-23 | 2021-06-23 |
+------------+----------------+--------------+
1 row in set (0.00 sec)
获取当前时间
- CURTIME()
- CURRENT_TIME()
- CURRENT_TIME
效果相同,获取当前时间:
mysql> SELECT CURTIME(),CURRENT_TIME(),CURRENT_TIME;
+-----------+----------------+--------------+
| CURTIME() | CURRENT_TIME() | CURRENT_TIME |
+-----------+----------------+--------------+
| 01:51:18 | 01:51:18 | 01:51:18 |
+-----------+----------------+--------------+
1 row in set (0.00 sec)
获取Unix时间戳
- UNIX_TIMESTAMP()
获取unix时间戳
mysql> SELECT UNIX_TIMESTAMP(), NOW(), UNIX_TIMESTAMP(NOW()), NOW(), UNIX_TIMESTAMP(NOW(3));
+------------------+---------------------+-----------------------+---------------------+------------------------+
| UNIX_TIMESTAMP() | NOW() | UNIX_TIMESTAMP(NOW()) | NOW() | UNIX_TIMESTAMP(NOW(3)) |
+------------------+---------------------+-----------------------+---------------------+------------------------+
| 1624419575 | 2021-06-23 03:39:35 | 1624419575 | 2021-06-23 03:39:35 | 1624419575.711 |
+------------------+---------------------+-----------------------+---------------------+------------------------+
1 row in set (0.00 sec)
需要注意的是,对于使用夏令时的地区,在时令交接处会存在两个时间对应同一个unix时间戳的现象:
mysql> SET time_zone = 'MET';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
| 1111885200 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
| 1111885200 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00 |
+---------------------------+
1 row in set (0.00 sec)
获取UTC标准日期时间
- UTC_DATE()
- UTC_DATE
获取UTC日期
mysql> SELECT UTC_DATE(),UTC_DATE;
+------------+------------+
| UTC_DATE() | UTC_DATE |
+------------+------------+
| 2021-06-23 | 2021-06-23 |
+------------+------------+
1 row in set (0.00 sec)
- UTC_TIME()
- UTC_TIME
获取UTC时间
mysql> SELECT UTC_TIME(),UTC_TIME;
+------------+----------+
| UTC_TIME() | UTC_TIME |
+------------+----------+
| 02:35:17 | 02:35:17 |
+------------+----------+
1 row in set (0.01 sec)
- UTC_TIMESTAMP()
相当于UTC下的NOW():
mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP()+0, UTC_TIMESTAMP(6) + 0, UTC_TIMESTAMP(3) + 0, NOW();
+---------------------+-------------------+-----------------------+----------------------+---------------------+
| UTC_TIMESTAMP() | UTC_TIMESTAMP()+0 | UTC_TIMESTAMP(6) + 0 | UTC_TIMESTAMP(3) + 0 | NOW() |
+---------------------+-------------------+-----------------------+----------------------+---------------------+
| 2021-06-23 03:34:11 | 20210623033411 | 20210623033411.186496 | 20210623033411.186 | 2021-06-23 03:34:11 |
+---------------------+-------------------+-----------------------+----------------------+---------------------+
从时间中提取部分信息
提取日期/时间
- DATE()
从日期时间中提取日期的部分:
mysql> SELECT DATE('2021-06-23 05:51:43'), DATE(NOW());
+-----------------------------+-------------+
| DATE('2021-06-23 05:51:43') | DATE(NOW()) |
+-----------------------------+-------------+
| 2021-06-23 | 2021-06-23 |
+-----------------------------+-------------+
- TIME()
从日期时间中提取时间的部分:
mysql> SELECT TIME('2021-06-23 05:51:43'), TIME(NOW());
+-----------------------------+-------------+
| TIME('2021-06-23 05:51:43') | TIME(NOW()) |
+-----------------------------+-------------+
| 05:51:43 | 05:54:19 |
+-----------------------------+-------------+
1 row in set (0.00 sec)
提取年/月/日/时/分/秒/微秒
- YEAR()
- MONTH()
- DAY()
- HOUR()
- MINUTE()
- SECOND()
从日期时间中提取年/月/日/时/分/秒:
mysql> 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()) |
+---------------------+-------------+--------------+------------+-------------+---------------+---------------+
| 2021-06-23 04:04:56 | 2021 | 6 | 23 | 4 | 4 | 56 |
+---------------------+-------------+--------------+------------+-------------+---------------+---------------+
- MICROSECOND()
从日期时间/时间中提取微秒:
mysql> SELECT NOW(6), MICROSECOND(NOW()), MICROSECOND(NOW(3)),MICROSECOND(NOW(6));
+----------------------------+--------------------+---------------------+---------------------+
| NOW(6) | MICROSECOND(NOW()) | MICROSECOND(NOW(3)) | MICROSECOND(NOW(6)) |
+----------------------------+--------------------+---------------------+---------------------+
| 2021-06-23 04:05:42.733499 | 0 | 733000 | 733499 |
+----------------------------+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)
- DAYOFMONTH()
同DAY()返回当前月份的第几天:
mysql> SELECT DAYOFMONTH('2021-06-23 06:13:10'), DAYOFMONTH(NOW()), DAYOFMONTH(CURDATE());
+-----------------------------------+-------------------+-----------------------+
| DAYOFMONTH('2021-06-23 06:13:10') | DAYOFMONTH(NOW()) | DAYOFMONTH(CURDATE()) |
+-----------------------------------+-------------------+-----------------------+
| 23 | 23 | 23 |
+-----------------------------------+-------------------+-----------------------+
1 row in set (0.00 sec)
提取当天是本星期/工作日/年度的第几天
- DAYOFWEEK()
返回当前星期的第几天(1 = Sunday, 2 = Monday, …, 7 = Saturday):
mysql> SELECT DAYOFWEEK('2021-06-23 06:13:10'), DAYOFWEEK(NOW()), DAYOFWEEK(CURDATE());
+----------------------------------+------------------+----------------------+
| DAYOFWEEK('2021-06-23 06:13:10') | DAYOFWEEK(NOW()) | DAYOFWEEK(CURDATE()) |
+----------------------------------+------------------+----------------------+
| 4 | 4 | 4 |
+----------------------------------+------------------+----------------------+
1 row in set (0.00 sec)
WEEKDAY()
返回是这周的第几天(0 = 星期一(Monday), 1 = 星期二(Tuesday), … 6 = 星期天(Sunday)).
注意和DAYOFWEEK()的区别,
mysql> SELECT DAYNAME('2021-06-21'), DAYOFWEEK('2021-06-21'), WEEKDAY('2021-06-21');
+-----------------------+-------------------------+-----------------------+
| DAYNAME('2021-06-21') | DAYOFWEEK('2021-06-21') | WEEKDAY('2021-06-21') |
+-----------------------+-------------------------+-----------------------+
| Monday | 2 | 0 |
+-----------------------+-------------------------+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT DAYNAME(NOW()), DAYOFWEEK(NOW()), WEEKDAY(NOW());
+----------------+------------------+----------------+
| DAYNAME(NOW()) | DAYOFWEEK(NOW()) | WEEKDAY(NOW()) |
+----------------+------------------+----------------+
| Thursday | 5 | 3 |
+----------------+------------------+----------------+
1 row in set (0.00 sec)
- DAYOFYEAR()
返回当前年的第几天
mysql> SELECT DAYOFYEAR('2021-06-23 06:13:10'), DAYOFYEAR(NOW()), DAYOFYEAR(CURDATE());
+----------------------------------+------------------+----------------------+
| DAYOFYEAR('2021-06-23 06:13:10') | DAYOFYEAR(NOW()) | DAYOFYEAR(CURDATE()) |
+----------------------------------+------------------+----------------------+
| 174 | 174 | 174 |
+----------------------------------+------------------+----------------------+
1 row in set (0.00 sec)
全能提取函数
- EXTRACT()
功能最强大的提取方法,用法EXTRACT(unit FROM date),unit指定需要提取的部分:
mysql> SELECT EXTRACT(YEAR FROM NOW()), EXTRACT(YEAR_MONTH FROM NOW()), EXTRACT(DAY_MINUTE FROM NOW()), EXTRACT(MICROSECOND FROM NOW(6));
+--------------------------+--------------------------------+--------------------------------+----------------------------------+
| EXTRACT(YEAR FROM NOW()) | EXTRACT(YEAR_MONTH FROM NOW()) | EXTRACT(DAY_MINUTE FROM NOW()) | EXTRACT(MICROSECOND FROM NOW(6)) |
+--------------------------+--------------------------------+--------------------------------+----------------------------------+
| 2021 | 202106 | 628 | 263982 |
+--------------------------+--------------------------------+--------------------------------+----------------------------------+
1 row in set (0.00 sec)
unit可选值请参考附录
返回星期几/月份名
- DAYNAME()
返回星期几:
mysql> SELECT DAYNAME('2021-06-23 06:13:10'), DAYNAME(NOW()), DAYNAME(CURDATE());
+--------------------------------+----------------+--------------------+
| DAYNAME('2021-06-23 06:13:10') | DAYNAME(NOW()) | DAYNAME(CURDATE()) |
+--------------------------------+----------------+--------------------+
| Wednesday | Wednesday | Wednesday |
+--------------------------------+----------------+--------------------+
1 row in set (0.00 sec)
- MONTHNAME()
返回月份名:
mysql> SELECT MONTHNAME('2021-06-23 06:13:10'), MONTHNAME(NOW()), MONTHNAME(CURDATE());
+----------------------------------+------------------+----------------------+
| MONTHNAME('2021-06-23 06:13:10') | MONTHNAME(NOW()) | MONTHNAME(CURDATE()) |
+----------------------------------+------------------+----------------------+
| June | June | June |
+----------------------------------+------------------+----------------------+
1 row in set (0.01 sec)
返回第几周,第几季度
- WEEK()
返回所在周是所在年的第几周,用法WEEK(date[,mode]):
mysql> SELECT WEEK('2008-02-20'),WEEK('2008-02-20',0),WEEK('2008-02-20',1);
+--------------------+----------------------+----------------------+
| WEEK('2008-02-20') | WEEK('2008-02-20',0) | WEEK('2008-02-20',1) |
+--------------------+----------------------+----------------------+
| 7 | 7 | 8 |
+--------------------+----------------------+----------------------+
1 row in set (0.00 sec)
其中mode参数的含义如下表:
Mode | 每周第一天是 | 返回值范围 | 每年的第一周是指第一个 … |
---|---|---|---|
0 | 周日 | 0-53 | 周日在本年的 |
1 | 周一 | 0-53 | 至少4天在本年的 |
2 | 周日 | 1-53 | 周日在本年的 |
3 | 周一 | 1-53 | 至少4天在本年的 |
4 | 周日 | 0-53 | 至少4天在本年的 |
5 | 周一 | 0-53 | 周日在本年的 |
6 | 周日 | 1-53 | 至少4天在本年的 |
7 | 周一 | 1-53 | 周日在本年的 |
结合mode含义,因为对每年第一周的认定方式不同,所以得到的结果也不同。
当省略mode参数,会使用mysql系统变量default_week_format的值作为mode的值。
还有一种情况值得注意,当一个日期的所在周被认定为是前一年的周:
mysql> SELECT WEEK('2008-01-01',0), WEEK('2008-01-01',2);
+----------------------+----------------------+
| WEEK('2008-01-01',0) | WEEK('2008-01-01',2) |
+----------------------+----------------------+
| 0 | 52 |
+----------------------+----------------------+
1 row in set (0.00 sec)
当使用0,1,4,5模式时,会得到0,这就是为什返回值的范围不同;
当使用2,3,6,7模式时,得到的值是此周在前一年的周数,这可以理解,但是在和其他函数一起使用时容易产生问题,因为返回的结果不是“日期所在周在日期所在年份的周数”,而是“日期所在周在日期所在周的所在年份的周数”。
这种情况下选择0还是前一年的周数,这是一个选择,如果偏向后者,还有一种办法:使用 YEARWEEK()方法。
- YEARWEEK()
返回一个日期的年份和周数,用法YEARWEEK(date[,mode])
mysql> SELECT YEARWEEK('2008-01-01'),YEARWEEK('2008-01-01',4),YEARWEEK('2008-01-01',5);
+------------------------+--------------------------+--------------------------+
| YEARWEEK('2008-01-01') | YEARWEEK('2008-01-01',4) | YEARWEEK('2008-01-01',5) |
+------------------------+--------------------------+--------------------------+
| 200752 | 200801 | 200753 |
+------------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)
mode参数和WEEK()方法非常相似,不同的是YEARWEEK()在mode参数缺省时,默认使用0,default_week_format的值不会影响YEARWEEK()方法。
另外当mode值为0或者1时, WEEK()函数可能会返回0,YEARWEEK()的week部分则不会:
mysql> SELECT WEEK('2008-01-01',0), YEARWEEK('2008-01-01',0);
+----------------------+--------------------------+
| WEEK('2008-01-01',0) | YEARWEEK('2008-01-01',0) |
+----------------------+--------------------------+
| 0 | 200752 |
+----------------------+--------------------------+
1 row in set (0.00 sec)
WEEKOFYEAR()
相当于WEEK()函数的WEEK(date,3)
- QUARTER()
返回季度:1:1月-3月,2:4月-6月,3:7月-9月,4:10月-12月
mysql> SELECT NOW(), QUARTER(NOW());
+---------------------+----------------+
| NOW() | QUARTER(NOW()) |
+---------------------+----------------+
| 2021-06-23 06:48:27 | 2 |
+---------------------+----------------+
1 row in set (0.01 sec)
计算日期时间
计算日期
DATE_ADD()
DATE_SUB()
这两个方法提供了日期的加减计算功能,用法如下:
DATE_ADD(date,INTERVAL expr unit),
DATE_SUB(date,INTERVAL expr unit)
其中date是计算的初始值;expr是时间间隔的数值部分,可以为负值;unit是时间间隔的单位部分,具体可选的取值请参考文末附录。
方法的返回值取决于入参的取值:
当入参date是纯日期,并且计算只涉及年月日,那么返回值就是纯日期:
mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 YEAR),DATE_SUB('2018-05-01',INTERVAL -1 YEAR);
+----------------------------------------+-----------------------------------------+
| DATE_ADD('2018-05-01',INTERVAL 1 YEAR) | DATE_SUB('2018-05-01',INTERVAL -1 YEAR) |
+----------------------------------------+-----------------------------------------+
| 2019-05-01 | 2019-05-01 |
+----------------------------------------+-----------------------------------------+
1 row in set (0.00 sec)
当入参是日期时间(DATETIME)(或者时间戳(TIMESTAMP))值,或年月日中涉及时间,返回值就是日期时间(DATETIME):
mysql> SELECT DATE_ADD('2100-12-31', INTERVAL '1:1' MINUTE_SECOND),DATE_ADD('2100-12-31', INTERVAL '-1:1' MINUTE_SECOND);
+------------------------------------------------------+-------------------------------------------------------+
| DATE_ADD('2100-12-31', INTERVAL '1:1' MINUTE_SECOND) | DATE_ADD('2100-12-31', INTERVAL '-1:1' MINUTE_SECOND) |
+------------------------------------------------------+-------------------------------------------------------+
| 2100-12-31 00:01:01 | 2100-12-30 23:58:59 |
+------------------------------------------------------+-------------------------------------------------------+
1 row in set (0.00 sec)
其他情况是String。
如果输入时DATE,但是想要DATETIME的返回,可以对入参使用CAST()函数
ADDDATE()
SUBDATE()
完全可以当作DATE_ADD()和DATE_SUB()来用,具体参考上文。
除此之外,他们还有另外的用法:
ADDDATE(expr,days),SUBDATE(expr,days)
此时days参数直接作为天数使用:
mysql> SELECT ADDDATE(NOW(),1),SUBDATE(NOW(),-1);
+---------------------+---------------------+
| ADDDATE(NOW(),1) | SUBDATE(NOW(),-1) |
+---------------------+---------------------+
| 2021-06-25 02:59:42 | 2021-06-25 02:59:42 |
+---------------------+---------------------+
1 row in set (0.00 sec)
计算时间
ADDTIME()
SUBTIME()
时间的加减法,用法是ADDTIME(expr1,expr2),其中expr1是时间或者日期时间,expr2是时间,可以有天数:
mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '-1 0:0:0.000000');
+----------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '-1 0:0:0.000000') |
+----------------------------------------------------------+
| 2007-12-30 23:59:59.999999 |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '0:0:0.000001');
+-------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '0:0:0.000001') |
+-------------------------------------------------------+
| 2008-01-01 00:00:00 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
计算年月(PERIOD)
PERIOD_ADD()
用法,PERIOD_ADD(P,N)
这里参数P是一个period,表示一个YYMM 或者 YYYYMM的年月,并非一个日期
函数的作用是给P增加N个月,返回一个YYYYMM,补上的两个YY为当前所在的YY:
mysql> SELECT PERIOD_ADD(192106,2), PERIOD_ADD(2106,2);
+----------------------+--------------------+
| PERIOD_ADD(192106,2) | PERIOD_ADD(2106,2) |
+----------------------+--------------------+
| 192108 | 202108 |
+----------------------+--------------------+
1 row in set (0.00 sec)
计算时间戳
TIMESTAMPADD()
用法:TIMESTAMPADD(unit,interval,datetime_expr)
把整数interval加到日期时间datetime_expr上,interval的单位通过unit指定。
unit的值需要时以下指定值之一:
MICROSECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, 或者 YEAR
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2021-06-24'),TIMESTAMPADD(WEEK,1,'2021-06-24');
+-------------------------------------+-----------------------------------+
| TIMESTAMPADD(MINUTE,1,'2021-06-24') | TIMESTAMPADD(WEEK,1,'2021-06-24') |
+-------------------------------------+-----------------------------------+
| 2021-06-24 00:01:00 | 2021-07-01 |
+-------------------------------------+-----------------------------------+
1 row in set (0.00 sec)
计算日期间隔
DATEDIFF()
计算第一个日期减去第二个日期的天数,时间部分会被直接忽略
mysql> SELECT DATEDIFF('2021-06-30 23:59:59','2021-06-29'),DATEDIFF('2021-05-31 23:59:59','2021-06-30');
+----------------------------------------------+----------------------------------------------+
| DATEDIFF('2021-06-30 23:59:59','2021-06-29') | DATEDIFF('2021-05-31 23:59:59','2021-06-30') |
+----------------------------------------------+----------------------------------------------+
| 1 | -30 |
+----------------------------------------------+----------------------------------------------+
1 row in set (0.00 sec)
计算时间间隔
TIMEDIFF()
计算两个时间或日期时间的差,两个参数必须同时是时间或同时是日期时间:
mysql> SELECT TIMEDIFF('2021-12-31 23:59:59.000001','2021-12-30 01:01:01.000002');
+---------------------------------------------------------------------+
| TIMEDIFF('2021-12-31 23:59:59.000001','2021-12-30 01:01:01.000002') |
+---------------------------------------------------------------------+
| 46:58:57.999999 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TIMEDIFF('23:59:59.000001','2021-12-30 01:01:01.000002');
+----------------------------------------------------------+
| TIMEDIFF('23:59:59.000001','2021-12-30 01:01:01.000002') |
+----------------------------------------------------------+
| NULL |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TIMEDIFF('2021-12-31 23:59:59.000001','01:01:01.000002');
+----------------------------------------------------------+
| TIMEDIFF('2021-12-31 23:59:59.000001','01:01:01.000002') |
+----------------------------------------------------------+
| NULL |
+----------------------------------------------------------+
1 row in set (0.00 sec)
时间的表示还支持这么一个格式:
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001');
+---------------------------------------------------------------+
| TIMEDIFF('2000:01:01 00:00:00', '2000:01:01 00:00:00.000001') |
+---------------------------------------------------------------+
| -00:00:00.000001 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
返回值的大小收到TIME大小的限制:
mysql> SELECT TIMEDIFF('2021-12-31 23:59:59.000001','2008-12-30 01:01:01.000002');
+---------------------------------------------------------------------+
| TIMEDIFF('2021-12-31 23:59:59.000001','2008-12-30 01:01:01.000002') |
+---------------------------------------------------------------------+
| 838:59:59.000000 |
+---------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT TIMEDIFF('2025-12-31 23:59:59.000001','2008-12-30 01:01:01.000002');
+---------------------------------------------------------------------+
| TIMEDIFF('2025-12-31 23:59:59.000001','2008-12-30 01:01:01.000002') |
+---------------------------------------------------------------------+
| 838:59:59.000000 |
+---------------------------------------------------------------------+
想要突破这种限制,可以使用 TIMESTAMPDIFF() 和UNIX_TIMESTAMP()函数,它们都返回整数。
计算月份间隔
PERIOD_DIFF()
计算两个年月相差的月
mysql> SELECT PERIOD_DIFF(202106,202103);
+----------------------------+
| PERIOD_DIFF(202106,202103) |
+----------------------------+
| 3 |
+----------------------------+
1 row in set (0.00 sec)
计算时间戳间隔
TIMESTAMPDIFF()
用法:TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
返回datetime_expr2 − datetime_expr1,datetime_expr2和datetime_expr1都是日期或者日期时间,如果是纯日期,在需要的时候会被当成拥有’00:00:00’这样的时间部分。返回结果的单位由参数unit指定,unit的可选取值参考 TIMESTAMPADD()方法。
mysql> SELECT TIMESTAMPDIFF(MONTH,'2021-06-24','2021-06-01'), TIMESTAMPDIFF(YEAR,'2020-06-24','2021-06-01');
+------------------------------------------------+-----------------------------------------------+
| TIMESTAMPDIFF(MONTH,'2021-06-24','2021-06-01') | TIMESTAMPDIFF(YEAR,'2020-06-24','2021-06-01') |
+------------------------------------------------+-----------------------------------------------+
| 0 | 0 |
+------------------------------------------------+-----------------------------------------------+
1 row in set (0.00 sec)
拼接时间戳
TIMESTAMP()
用法:TIMESTAMP(expr1[,expr2])
expr1是一个日期或者日期时间,expr2是时间,如果省略,当成‘ 00:00:00 ’
返回把expr2加到expr1上的结果
mysql> SELECT TIMESTAMP('2021-06-24'),TIMESTAMP('2021-06-24 12:00:00','12:00:00');
+-------------------------+---------------------------------------------+
| TIMESTAMP('2021-06-24') | TIMESTAMP('2021-06-24 12:00:00','12:00:00') |
+-------------------------+---------------------------------------------+
| 2021-06-24 00:00:00 | 2021-06-25 00:00:00 |
+-------------------------+---------------------------------------------+
1 row in set (0.00 sec)
计算当月最后一天
- LAST_DAY()
返回日期所在月的最后一天
mysql> select LAST_DAY(NOW()),LAST_DAY('2021-02-00');
+-----------------+------------------------+
| LAST_DAY(NOW()) | LAST_DAY('2021-02-00') |
+-----------------+------------------------+
| 2021-06-30 | 2021-02-28 |
+-----------------+------------------------+
1 row in set (0.00 sec)
转换
转换时区
CONVERT_TZ()
用法:CONVERT_TZ(dt,from_tz,to_tz)
将dt从from_tz时区转换成to_tz时区,关于mysql时区,参考我的这篇文章,如果参数无效,函数返回NULL。
如果在from_tz到UTC的转换时,结果超出了TIMESTAMP的取值范围(‘1970-01-01 00:00:01.000000’ - ‘2038-01-19 03:14:07.999999’),那么不会发生转换。
mysql> SELECT CONVERT_TZ('1970-01-01 08:00:01','Asia/Shanghai','UTC');
+---------------------------------------------------------+
| CONVERT_TZ('1970-01-01 08:00:01','Asia/Shanghai','UTC') |
+---------------------------------------------------------+
| 1970-01-01 00:00:01 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONVERT_TZ('1970-01-01 08:00:01','+8:00','SYSTEM');
+----------------------------------------------------+
| CONVERT_TZ('1970-01-01 08:00:01','+8:00','SYSTEM') |
+----------------------------------------------------+
| 1970-01-01 00:00:01 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT CONVERT_TZ('1970-01-01 08:00:00','Asia/Shanghai','UTC'); -- no conversion occurs
+---------------------------------------------------------+
| CONVERT_TZ('1970-01-01 08:00:00','Asia/Shanghai','UTC') |
+---------------------------------------------------------+
| 1970-01-01 08:00:00 |
+---------------------------------------------------------+
1 row in set (0.00 sec)
日期天数互转
TO_DAYS()
返回从0年到指定日期的天数,对于1582(格里高利历颁行)之前的年份,这个函数并不可靠。
参数可以只传两位年份,会被自动补全到四位年份,补全规则如下:
- 70-99 自动补全为 1970-1999.
- 00-69 自动补全为 2000-2069.
mysql> SELECT TO_DAYS('2021-10-07'), TO_DAYS('21-10-07'), TO_DAYS(211007);
+-----------------------+---------------------+-----------------+
| TO_DAYS('2021-10-07') | TO_DAYS('21-10-07') | TO_DAYS(211007) |
+-----------------------+---------------------+-----------------+
| 738435 | 738435 | 738435 |
+-----------------------+---------------------+-----------------+
1 row in set (0.00 sec)
另外’0000-00-00’ 是不行的,而 '0000-01-01’是可以的,因为00月00日是不存在的而0000年时存在的。并且这和 SQL Mode ALLOW_INVALID_DATES 无关
mysql> SELECT TO_DAYS('0000-00-00');
+-----------------------+
| to_days('0000-00-00') |
+-----------------------+
| NULL |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_DAYS('0000-01-01');
+-----------------------+
| to_days('0000-01-01') |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
FROM_DAYS()
和TO_DAYS()相反的函数:
mysql> SELECT FROM_DAYS(738331), TO_DAYS('2021-06-25');
+-------------------+-----------------------+
| FROM_DAYS(738331) | TO_DAYS('2021-06-25') |
+-------------------+-----------------------+
| 2021-06-25 | 738331 |
+-------------------+-----------------------+
1 row in set (0.00 sec)
日期时间和秒数(Unix时间戳)互转
TO_SECONDS()
转日期时间成从0年到现在的秒
和TO_DAYS()方法类似,注意三点:
1582年前不靠谱,2位年份自动补成4位年份,‘0000-00-00’ 不行。
mysql> SELECT TO_SECONDS('2021-06-25 07:02:01') , TO_SECONDS('1970-01-01 00:00:00');
+-----------------------------------+-----------------------------------+
| TO_SECONDS('2021-06-25 07:02:01') | TO_SECONDS('1970-01-01 00:00:00') |
+-----------------------------------+-----------------------------------+
| 63791823721 | 62167219200 |
+-----------------------------------+-----------------------------------+
1 row in set (0.00 sec)
FROM_UNIXTIME()
用法:FROM_UNIXTIME(unix_timestamp[,format])
unix_timestamp是一个Unix时间戳。函数返回一个由unix_timestamp转换的日期时间或者字符串。字符串的格式由format指定。返回结果使用的时区是当前会话的时区(current session time zone)。关于MySQL可参考我的这篇文章。
format的用法可参考DATE_FORMAT()函数用法。
不同于FROM_DAYS()和TO_DAYS()两个函数的互为逆向操作
TO_SECONDS()和FROM_UNIXTIME()并非完全互为逆向操作,有两点不同:
- 起点不同,TO_SECONDS()起点是0年,而FROM_UNIXTIME()起点是1970-01-01 00:00:00
- 时区概念不同,TO_SECONDS()不考虑时区,而FROM_UNIXTIME()考虑时区
第一点好理解,直接结合SQL语句来看:
mysql> SELECT TO_SECONDS('2021-06-25 07:02:01') - TO_SECONDS('1970-01-01 00:00:00');
+-----------------------------------------------------------------------+
| TO_SECONDS('2021-06-25 07:02:01') - TO_SECONDS('1970-01-01 00:00:00') |
+-----------------------------------------------------------------------+
| 1624604521 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1624604521);
+---------------------------+
| FROM_UNIXTIME(1624604521) |
+---------------------------+
| 2021-06-25 07:02:01 |
+---------------------------+
1 row in set (0.00 sec)
接下来说第二条
因为Unix时间戳的起点是一个具体的时间点:1970年1月1日(UTC/GMT的午夜)。对于不同时区,这个时间点的表示是不一样的。所以如果不是UTC/GMT,那么两个函数的起点是不一样的。上面SQL的日期时间和秒数的互转是成立是因为我使用的是UTC时区。如果不是,我还是直观地用SQL来展示:
mysql> SET time_zone = 'Asia/Shanghai';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT TO_SECONDS('2021-06-25 07:02:01') - TO_SECONDS('1970-01-01 00:00:00');
+-----------------------------------------------------------------------+
| TO_SECONDS('2021-06-25 07:02:01') - TO_SECONDS('1970-01-01 00:00:00') |
+-----------------------------------------------------------------------+
| 1624604521 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1624604521);
+---------------------------+
| FROM_UNIXTIME(1624604521) |
+---------------------------+
| 2021-06-25 15:02:01 |
+---------------------------+
1 row in set (0.00 sec)
可以看到当将时区设置为东八区,转换回来的结果差了8小时。如果想原样转回来应该是这样的:
mysql> SET time_zone = 'Asia/Shanghai';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT FROM_UNIXTIME(TO_SECONDS(CONVERT_TZ('2021-06-25 07:02:01','Asia/Shanghai','UTC')) - TO_SECONDS('1970-01-01 00:00:00'));
+------------------------------------------------------------------------------------------------------------------------+
| FROM_UNIXTIME(TO_SECONDS(CONVERT_TZ('2021-06-25 07:02:01','Asia/Shanghai','UTC')) - TO_SECONDS('1970-01-01 00:00:00')) |
+------------------------------------------------------------------------------------------------------------------------+
| 2021-06-25 07:02:01 |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
这样就可以转换回来,本来最好理解的是将1970-01-01 00:00:00这个起点转换成东八区时间的,这样整个语境就都是在东八区了。但是正好CONVERT_TZ()函数对小于1970-01-01 00:00:00的日期时间支持有问题。所以只能这么转换。
解释一下这个转换过程,假设时间静止在东八区时间是’2021-06-25 07:02:01’时,通过CONVERT_TZ()得到UTC此时的时间值,然后减去Unix时间戳的起点’1970-01-01 00:00:00’,这个也是UTC时区的时间值,所以得到的结果就是这个静止的时间点到Unix时间戳的起点的差,也就是当前静止时间点的Unix时间戳。再通过FROM_UNIXTIME()就可以得到这个静止时间点的东八区的日期时间值。
说得有点绕,对照SQL意会吧!
时间秒数互转
TIME_TO_SEC()
时间转秒数:
mysql> SELECT TIME_TO_SEC('22:23:00');
+-------------------------+
| TIME_TO_SEC('22:23:00') |
+-------------------------+
| 80580 |
+-------------------------+
1 row in set (0.00 sec)
SEC_TO_TIME()
秒数转时间,返回值受时间的取值范围(’-838:59:59’ to ‘838:59:59’)限制:
mysql> SELECT SEC_TO_TIME(1234567);
+----------------------+
| SEC_TO_TIME(1234567) |
+----------------------+
| 342:56:07 |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(12345678);
+-----------------------+
| SEC_TO_TIME(12345678) |
+-----------------------+
| 838:59:59 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
根据年份和年份第几天创建日期
MAKEDATE()
用法:MAKEDATE(year,dayofyear)
返回一个日期值,dayofyear参数必须大于0,否则返回值会是NULL:
mysql> SELECT MAKEDATE(2021,365),MAKEDATE(2021,0);
+--------------------+------------------+
| MAKEDATE(2021,365) | MAKEDATE(2021,0) |
+--------------------+------------------+
| 2021-12-31 | NULL |
+--------------------+------------------+
1 row in set (0.00 sec)
根据时分秒(毫秒)创建时间
MAKETIME()
根据时分秒返回一个时间值,秒可以有小数:
mysql> SELECT MAKETIME(12,15,30), MAKETIME(12,15,30.1234);
+--------------------+-------------------------+
| MAKETIME(12,15,30) | MAKETIME(12,15,30.1234) |
+--------------------+-------------------------+
| 12:15:30 | 12:15:30.1234 |
+--------------------+-------------------------+
1 row in set (0.00 sec)
日期/时间转字符串(格式化)
DATE_FORMAT()
用法DATE_FORMAT(date,format),将date格式化为format字符串的格式。format字符串由一系列描述符构成,描述符列表可参考附录。描述符前的%是必需的。
对于%U, %u, %V 和 %v四个描述符的具体含义,可以参考本文WEEK()函数部分。
关于星期几和月份名用什么语言来展示,由mysql系统变量lc_time_names来控制。
mysql> SELECT @@lc_time_names,DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+-----------------+------------------------------------------------+
| @@lc_time_names | DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') |
+-----------------+------------------------------------------------+
| en_US | Sunday October 2009 |
+-----------------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> SET lc_time_names = 'zh_CN';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@lc_time_names,DATE_FORMAT('2009-10-04 22:23:00', '%W %M %H');
+-----------------+------------------------------------------------+
| @@lc_time_names | DATE_FORMAT('2009-10-04 22:23:00', '%W %M %H') |
+-----------------+------------------------------------------------+
| zh_CN | 星期日 十月 22 |
+-----------------+------------------------------------------------+
1 row in set (0.00 sec)
对于格式化描述符表中的最后一条,是指如果不是描述符,就原样显示:
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %G'),DATE_FORMAT('2009-10-04 22:23:00', '%W %M hello world!');
+------------------------------------------------+----------------------------------------------------------+
| DATE_FORMAT('2009-10-04 22:23:00', '%W %M %G') | DATE_FORMAT('2009-10-04 22:23:00', '%W %M hello world!') |
+------------------------------------------------+----------------------------------------------------------+
| 星期日 十月 G | 星期日 十月 hello world! |
+------------------------------------------------+----------------------------------------------------------+
1 row in set (0.00 sec)
TIME_FORMAT()
和DATE_FORMAT()函数类似,不过只格式化时间部分,只能适用与时间相关的格式描述符。除此之外的其他的描述符会产生NULL或者0。
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l %w');
+-----------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l %w') |
+-----------------------------------------------+
| NULL |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l %y');
+-----------------------------------------------+
| TIME_FORMAT('100:00:00', '%H %k %h %I %l %y') |
+-----------------------------------------------+
| 100 100 04 04 4 00 |
+-----------------------------------------------+
1 row in set (0.00 sec)
对于大于23的小时数,%H 和 %k描述符会返回大于0…23的通常小时的范围。其他的小时描述符会返回12取模。
mysql> SELECT TIME_FORMAT('112:00:00', '%H %k %h %I %l');
+--------------------------------------------+
| TIME_FORMAT('112:00:00', '%H %k %h %I %l') |
+--------------------------------------------+
| 112 112 04 04 4 |
+--------------------------------------------+
1 row in set (0.00 sec)
字符串转日期
STR_TO_DATE()
DATE_FORMAT()函数的逆向操作。
用法:STR_TO_DATE(str,format)
函数通过format来判断返回结果,如果其中日期时间两部分,就返回日期时间,只有其中一个就返回其中一个(实测使用纯时间必须没有开启sql_mode:NO_ZERO_DATE,否则函数会返回NULL)。如果从str中提取的日期时间不合法,函数会返回NULL并产生一个警告。
MySQL server会扫描str来尝试匹配format。格式化字符串可以包含字面量字符和以%开头的格式化描述符,format中的字面量必须匹配str中的字面量,format中的格式化描述符必须匹配str中日期或时间的对应部分。可用的格式化描述符请参考文末附录。
mysql> SELECT STR_TO_DATE('20,5,2021','%d,%m,%Y');
+-------------------------------------+
| STR_TO_DATE('20,5,2021','%d,%m,%Y') |
+-------------------------------------+
| 2021-05-20 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('May 20, 2021','%M %d,%Y');
+----------------------------------------+
| STR_TO_DATE('May 20, 2021','%M %d,%Y') |
+----------------------------------------+
| 2021-05-20 |
+----------------------------------------+
1 row in set (0.00 sec)
从左向右扫描str,如果发现和format字符串不匹配就失败。结尾的多余字符会被忽略:
mysql> SELECT STR_TO_DATE('abcdeMay 20, 2021 09:30:17','abcde%M %d,%Y %h:%i:%s');
+--------------------------------------------------------------------+
| STR_TO_DATE('abcdeMay 20, 2021 09:30:17','abcde%M %d,%Y %h:%i:%s') |
+--------------------------------------------------------------------+
| 2021-05-20 09:30:17 |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('abcdeMay 20, 2021 09:30:17','%M %d,%Y %h:%i:%s');
+---------------------------------------------------------------+
| STR_TO_DATE('abcdeMay 20, 2021 09:30:17','%M %d,%Y %h:%i:%s') |
+---------------------------------------------------------------+
| NULL |
+---------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT STR_TO_DATE('May 20, 2021 09:30:17abcde','%M %d,%Y %h:%i:%s');
+---------------------------------------------------------------+
| STR_TO_DATE('May 20, 2021 09:30:17abcde','%M %d,%Y %h:%i:%s') |
+---------------------------------------------------------------+
| 2021-05-20 09:30:17 |
+---------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
接下来看看sql_mode:NO_ZERO_DATE对STR_TO_DATE()函数的影响,首先不允许年月日为0,甚至不允许只有时间
mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT STR_TO_DATE('abc','abc');
+--------------------------+
| STR_TO_DATE('abc','abc') |
+--------------------------+
| 0000-00-00 |
+--------------------------+
1 row in set (0.01 sec)
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
+-------------------------------------+
| STR_TO_DATE('09:30:17a','%h:%i:%s') |
+-------------------------------------+
| 09:30:17 |
+-------------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> SET sql_mode = 'NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT STR_TO_DATE('abc','abc');
+--------------------------+
| STR_TO_DATE('abc','abc') |
+--------------------------+
| NULL |
+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT STR_TO_DATE('09:30:17','%h:%i:%s');
+------------------------------------+
| STR_TO_DATE('09:30:17','%h:%i:%s') |
+------------------------------------+
| NULL |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)
获取描述格式的字符串
GET_FORMAT()
用法:GET_FORMAT({DATE|TIME|DATETIME}, {‘EUR’|‘USA’|‘JIS’|‘ISO’|‘INTERNAL’})
返回一个用于指定时间格式的字符串
两个参数一共有3*5=15种组合结果,如下:
Function Call | Result |
---|---|
GET_FORMAT(DATE,‘USA’) | ‘%m.%d.%Y’ |
GET_FORMAT(DATE,‘JIS’) | ‘%Y-%m-%d’ |
GET_FORMAT(DATE,‘ISO’) | ‘%Y-%m-%d’ |
GET_FORMAT(DATE,‘EUR’) | ‘%d.%m.%Y’ |
GET_FORMAT(DATE,‘INTERNAL’) | ‘%Y%m%d’ |
GET_FORMAT(DATETIME,‘USA’) | ‘%Y-%m-%d %H.%i.%s’ |
GET_FORMAT(DATETIME,‘JIS’) | ‘%Y-%m-%d %H:%i:%s’ |
GET_FORMAT(DATETIME,‘ISO’) | ‘%Y-%m-%d %H:%i:%s’ |
GET_FORMAT(DATETIME,‘EUR’) | ‘%Y-%m-%d %H.%i.%s’ |
GET_FORMAT(DATETIME,‘INTERNAL’) | ‘%Y%m%d%H%i%s’ |
GET_FORMAT(TIME,‘USA’) | ‘%h:%i:%s %p’ |
GET_FORMAT(TIME,‘JIS’) | ‘%H:%i:%s’ |
GET_FORMAT(TIME,‘ISO’) | ‘%H:%i:%s’ |
GET_FORMAT(TIME,‘EUR’) | ‘%H.%i.%s’ |
GET_FORMAT(TIME,‘INTERNAL’) | ‘%H%i%s’ |
这个方法和DATE_FORMAT() , STR_TO_DATE()这两个方法配合的时候很有用:
mysql> SELECT DATE_FORMAT('2021-06-28',GET_FORMAT(DATE,'USA'));
+--------------------------------------------------+
| DATE_FORMAT('2021-06-28',GET_FORMAT(DATE,'USA')) |
+--------------------------------------------------+
| 06.28.2021 |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT STR_TO_DATE('06.28.2021',GET_FORMAT(DATE,'USA'));
+--------------------------------------------------+
| STR_TO_DATE('06.28.2021',GET_FORMAT(DATE,'USA')) |
+--------------------------------------------------+
| 2021-06-28 |
+--------------------------------------------------+
1 row in set (0.00 sec)
附录
格式化描述符
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 | Hour (01…12) |
%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 | Seconds (00…59) |
%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 |
所有时间日期函数简表
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 | 同CURDATE() | Synonyms for CURDATE() |
CURRENT_TIME(), CURRENT_TIME | 同CURTIME() | Synonyms for CURTIME() |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | 同NOW() | 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() | 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() | 转换unix时间戳为日期时间 | 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() | Synonym for NOW() |
LOCALTIMESTAMP, LOCALTIMESTAMP() | Synonym for NOW() | 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() | 转换秒数到 ‘hh:mm:ss’ 形式表示的时间 | Converts seconds to ‘hh:mm:ss’ format |
SECOND() | 抽取时间部分的秒数 | Return the second (0-59) |
STR_TO_DATE() | 转换字符串为日期时间 | Convert a string to a date |
SUBDATE() | 当传入三个参数的时候同DATE_SUB() | 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() | 返回从第0年到现在的天数 | Return the date argument converted to days |
TO_SECONDS() | 返回从第0年到现在的秒数 | Return the date or datetime argument converted to seconds since Year 0 |
UNIX_TIMESTAMP() | 返回unix时间戳 | Return a Unix timestamp |
UTC_DATE() | 返回当前UTC日期 | Return the current UTC date |
UTC_TIME() | 返回当前UTC时间 | Return the current UTC time |
UTC_TIMESTAMP() | 返回当前UTC日期时间 | 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 |
unit可选值
unit可选值 | 对应表达式格式 |
---|---|
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’ |
最后,若有疏漏,请各位看官务必大力开喷,我一定改进。
另附官方文档在此,以备不时之需。