MySQL函数&关键字（七）时间计算/转换 date_add()/diff/format+时间戳

MySQL 官方手册 8.0 Reference Manual - DATE_FORMAT(date,format)

• 时间计算

#1.增减间隔 DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)
set @dt = now();

select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
select date_add(@dt, interval 1 minute); -- ...
select date_add(@dt, interval 1 second);
select date_add(@dt, interval 1 microsecond);
select date_add(@dt, interval 1 week);
select date_add(@dt, interval 1 month);
select date_add(@dt, interval 1 quarter);
select date_add(@dt, interval 1 year);

select date_add(@dt, interval -1 day); -- sub 1 day

select date_add(@dt, interval '1 01:15:30' day_second); -- add 1天 1小时 15分 30秒
# MySQL adddate(), addtime()函数，可以用 date_add() 来替代。subdate(), subtime()，建议，用 date_sub() 来替代。

# 2. MySQL 日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)
# MySQL datediff(date1,date2)：两个日期相减 date1 - date2，返回天数。
select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7

# MySQL timediff(time1,time2)：两个日期相减 time1 - time2，返回 time 差值。
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08

# 注意：timediff(time1,time2) 函数的两个参数类型必须相同。

• 转换函数

  • MySQL Date/Time to Str

# MySQL Date/Time to Str（日期/时间转换为字符串）函数：date_format(date,format), time_format(time,format)
select date_format('2008-08-08 22:23:00', '%W %M %Y'); -- Friday August 2008 
select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s'); -- 20080808222301
select time_format('22:23:01', '%H.%i.%s'); -- 22.23.01

• MySQL Str to Date

# MySQL Str to Date （字符串转换为日期）函数：str_to_date(str, format)
# 这是DATE_FORMAT()函数的反 函数。它需要一个字符串str和一个格式字符串format。 
# 如果格式字符串包含日期和时间部分，则STR_TO_DATE()返回一个 DATETIME值，
# 如果字符串只包含日期或时间部分，则返回一个 DATE或 TIME值。
# 如果从中提取的日期、时间或日期时间值不str 合法，则STR_TO_DATE() 返回NULL并产生警告。
select str_to_date('08/09/2008', '%m/%d/%Y'); -- 2008-08-09
select str_to_date('08/09/08' , '%m/%d/%y'); -- 2008-08-09
select str_to_date('08.09.2008', '%m.%d.%Y'); -- 2008-08-09
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s'); -- 2008-08-09 08:09:30

说明符	描述
%a	缩写的工作日名称 ( Sun… Sat)
%b	缩写的月份名称 ( Jan… Dec)
%c	月份，数字 ( 0… 12)
%D	带有英文后缀 ( 0th, 1st, 2nd, 3rd, …)
%d	月份中的第几天，数字 ( 00… 31)
%e	月份中的第几天，数字 ( 0… 31)
%f	微秒 ( 000000… 999999)
%H	小时 ( 00… 23)
%h	小时 ( 01… 12)
%I	小时 ( 01… 12)
%i	分钟，数字 ( 00… 59)
%j	一年中的某一天 ( 001… 366)
%k	小时 ( 0… 23)
%l	小时 ( 1… 12)
%M	月份名称 ( January… December)
%m	月份，数字 ( 00… 12)
%p	AM 或者 PM
%r	时间，12 小时制（*hh:mm:ss*后跟 AM或PM）
%S	秒 ( 00… 59)
%s	秒 ( 00… 59)
%T	时间，24 小时制 ( hh:mm:ss)
%U	周 ( 00… 53)，其中星期日是一周的第一天； WEEK()模式 0
%u	周 ( 00… 53)，其中星期一是一周的第一天； WEEK()模式1
%V	周 ( 01… 53)，其中星期日是一周的第一天； WEEK()模式2；与 %X
%v	周 ( 01… 53)，其中星期一是一周的第一天； WEEK()模式3；与 %x
%W	工作日名称 ( Sunday… Saturday)
%w	星期几（0=星期日… 6=星期六）
%X	周的年份，其中星期日是一周的第一天，数字，四位数字；与%V
%x	一周的年份，其中星期一是一周的第一天，数字，四位数字；与%v
%Y	年份，数字，四位数字
%y	年份，数字（两位数）
%%	文字%字符
%x	x, 对于上面未列出的任何 “ x”

• MAKEDATE(year,dayofyear)

返回日期，给定年份和年份值。 *dayofyear*必须大于 0 或结果为NULL。

mysql> SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
        -> '2011-01-31', '2011-02-01'
mysql> SELECT MAKEDATE(2011,365), MAKEDATE(2014,365);
        -> '2011-12-31', '2014-12-31'
mysql> SELECT MAKEDATE(2011,0);
        -> NULL

• MAKETIME(hour,minute,second)

返回从计算出的时间值 hour， *minute*和 *second*参数。

该*second*参数可以有小数部分。

mysql> SELECT MAKETIME(12,15,30);
        -> '12:15:30'

• 时间戳

# 1. MySQL 获得当前时间戳函数：current_timestamp, current_timestamp()
mysql> select current_timestamp, current_timestamp();

+---------------------+---------------------+
| current_timestamp   | current_timestamp() |
+---------------------+---------------------+
| 2008-08-09 23:22:24 | 2008-08-09 23:22:24 |
+---------------------+---------------------+

# 2. MySQL （Unix 时间戳、日期）转换函数：
unix_timestamp(),
unix_timestamp(date),
from_unixtime(unix_timestamp),
from_unixtime(unix_timestamp,format)

# 下面是示例：
select unix_timestamp(); -- 1218290027
select unix_timestamp('2008-08-08'); -- 1218124800
select unix_timestamp('2008-08-08 12:30:00'); -- 1218169800

select from_unixtime(1218290027); -- '2008-08-09 21:53:47'
select from_unixtime(1218124800); -- '2008-08-08 00:00:00'
select from_unixtime(1218169800); -- '2008-08-08 12:30:00'

select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x'); -- '2008 8th August 12:30:00 2008'

• TIMESTAMP(expr), TIMESTAMP(expr1,expr2)

使用单个参数，此函数将日期或日期时间表达式*expr*作为日期时间值返回。

使用两个参数，它将时间表达式添加*expr2到日期或日期时间表达式expr1*，并将结果作为日期时间值返回。

mysql> SELECT TIMESTAMP('2003-12-31');
        -> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
        -> '2004-01-01 00:00:00'

• TIMESTAMPADD(unit,interval,datetime_expr)

将整数表达式添加 interval到日期或日期时间表达式datetime_expr。单位*interval由unit*参数给出， 参数应为以下值之一：（MICROSECOND 微秒）SECOND、 MINUTE、HOUR、 DAY、WEEK、 MONTH、QUARTER、 或 YEAR。

*unit*可以使用所示的关键字之一指定 该值，或者使用前缀 SQL_TSI_. 例如， DAY并且SQL_TSI_DAY都是合法的。

mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
        -> '2003-01-02 00:01:00'
mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
        -> '2003-01-09'

• TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

返回*datetime_expr2*- datetime_expr1，其中 *datetime_expr1*和 *datetime_expr2是日期或日期时间表达式。一个表达式可能是日期，另一个表达式可能是日期时间；日期值被视为'00:00:00'在必要时具有时间部分的日期时间。结果的单位（整数）由unit参数给出 。的合法值unit*与TIMESTAMPADD()函数说明中列出的值 相同 。

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
        -> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
        -> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
        -> 128885

此函数的日期或日期时间参数的顺序与使用 2 个参数的TIMESTAMP()函数相反 。

MySQL timestampdiff() 函数就比 datediff() 功能强多了，datediff() 只能计算两个日期（date）之间相差的天数。