本文问题

1. 在MySQL中，时间间隔用什么格式来表示？
2. 时间间隔的单位有哪些？分别表示什么意义？
3. 如何获取两个日期/时间之间的间隔？
4. 更改日期和时间有哪几种方法？
5. 哪些函数可以用来更改日期？
6. 哪些函数可以用来更改时间？
7. 哪些函数可以用来更改日期和时间？

时间间隔

interval_expr表达式用来代表时间间隔，具体语法如下：
INTERVAL expr unit
expr表示数量 unit表示单位

时间间隔单位及对应的数值格式

说明	unit值	expr格式
微秒	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'

获取时间间隔

• PERIOD_DIFF(p1,p2) 月份间隔
  p1,p2格式为YYMM或YYYYMM
  结果为p1-p2
• DATEDIFF(expr1,expr2) 日期间隔
  p1,p2格式为日期或日期时间，但是只使用其中的日期部分，时间部分会被忽略。
  结果为expr1-expr2的天数值
• TIMEDIFF(expr1,expr2) 时间间隔
  expr1,expr2格式为时间或日期时间，两个格式必须相同。
  结果为expr1-expr2的时间值，结果值不能超过TIME格式的范围
• TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) 日期时间间隔
  datetime_expr1,datetime_expr2格式为日期或日期时间，

# PERIOD_DIFF
mysql> select period_diff('202004','202009');
+--------------------------------+
| period_diff('202004','202009') |
+--------------------------------+
|                             -5 |
+--------------------------------+
1 row in set (0.00 sec)
# DATEDIFF
mysql> select datediff(20200119,20200222);
+-----------------------------+
| datediff(20200119,20200222) |
+-----------------------------+
|                         -34 |
+-----------------------------+
1 row in set (0.00 sec)
# TIMEDIFF
mysql>  select timediff('20200111120000','20200211011200');
+---------------------------------------------+
| timediff('20200111120000','20200211011200') |
+---------------------------------------------+
| -733:12:00                                  |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql>  select timediff('20200111120000','20200311011200');
+---------------------------------------------+
| timediff('20200111120000','20200311011200') |
+---------------------------------------------+
| -838:59:59                                  |
+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)
# TIMESTAMPDIFF
mysql> select timestampdiff(day,20200119,20200222121122);
+--------------------------------------------+
| timestampdiff(day,20200119,20200222121122) |
+--------------------------------------------+
|                                         34 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select timestampdiff(second,20200119,20200222121122);
+-----------------------------------------------+
| timestampdiff(second,20200119,20200222121122) |
+-----------------------------------------------+
|                                       2981482 |
+-----------------------------------------------+
1 row in set (0.00 sec)

更改时间

使用+/- INTERVAL更改时间

不能用时间间隔减去时间

mysql> SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;
        -> '2019-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '2018-12-31';
        -> '2019-01-01'
mysql> SELECT '2025-01-01' - INTERVAL 1 SECOND;
        -> '2024-12-31 23:59:59'

使用函数更改时间

只更改日期

• ADDDATE(date,INTERVAL expr unit)=DATE_ADD(date,INTERVAL expr unit) /SUBDATE(date,INTERVAL expr unit)=DATE_SUB(date,INTERVAL expr unit)
  增加/减少INTERVAL expr unit指定的时间间隔
• ADDDATE(expr,days) / SUBDATE(expr,days)
  增加/减少days天数
• PERIOD_ADD(P,N)
  增加月份 P格式要求为YYYYMM/YYMM

mysql> select adddate('2020-01-02',23),date_add('2020-01-02',interval 23 day);
+--------------------------+----------------------------------------+
| adddate('2020-01-02',23) | date_add('2020-01-02',interval 23 day) |
+--------------------------+----------------------------------------+
| 2020-01-25               | 2020-01-25                             |
+--------------------------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select adddate('2020-01-02',interval 10 month),period_add('20200102',10);
+-----------------------------------------+---------------------------+
| adddate('2020-01-02',interval 10 month) | period_add('20200102',10) |
+-----------------------------------------+---------------------------+
| 2020-11-02                              |                  20200112 |
+-----------------------------------------+---------------------------+
1 row in set (0.00 sec)

只更改时间

• ADDTIME(expr1,expr2)/SUBTIME(expr1,expr2) 增加/减少时间

mysql> SELECT ADDTIME('2007-12-31 23:59:59', '1 1:1:1'),ADDTIME('23:59:59', '1 1:1:1.999999');
+-------------------------------------------+---------------------------------------+
| ADDTIME('2007-12-31 23:59:59', '1 1:1:1') | ADDTIME('23:59:59', '1 1:1:1.999999') |
+-------------------------------------------+---------------------------------------+
| 2008-01-02 01:01:00                       | 49:01:00.999999                       |
+-------------------------------------------+---------------------------------------+
1 row in set (0.00 sec)

增加日期时间间隔

• TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
TIMESTAMP(expr)将expr转换为DATETIME格式
  TIMESTAMP(expr1,expr2) expr1格式为日期或日期时间，expr2格式为时间
• TIMESTAMPADD(unit,interval,datetime_expr)
unit：MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR.

mysql> select timestamp('20200102'),timestamp('20200102','12:00:01'),timestamp('2020-01-02 00:00:01','12:00:01');
+-----------------------+----------------------------------+---------------------------------------------+
| timestamp('20200102') | timestamp('20200102','12:00:01') | timestamp('2020-01-02 00:00:01','12:00:01') |
+-----------------------+----------------------------------+---------------------------------------------+
| 2020-01-02 00:00:00   | 2020-01-02 12:00:01              | 2020-01-02 12:00:02                         |
+-----------------------+----------------------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select timestampadd(day,12,'20200102'),timestampadd(minute,12,'20200102');
+---------------------------------+------------------------------------+
| timestampadd(day,12,'20200102') | timestampadd(minute,12,'20200102') |
+---------------------------------+------------------------------------+
| 2020-01-14                      | 2020-01-02 00:12:00                |
+---------------------------------+------------------------------------+
1 row in set (0.00 sec)

更改时区

CONVERT_TZ(dt,from_tz,to_tz)
将dt从from_tz更新为to_tz时区的DATETIME值

mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        -> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
        -> '2004-01-01 22:00:00'

问题答案

1. 在MySQL中，时间间隔用什么格式来表示？
   INTERVAL expr unit expr表示数值 unit表示expr的单位
2. 时间间隔的单位有哪些？分别表示什么意义？
   单一单位：microsecond,second,minute,hour,day,week,month,quarter,year
   多种单位：名称格式为：时间较长的单位_时间较短的单位
   时间相关间隔可以从小时到微秒。
   second_microsecond,minute_microsecond,minute_second,hour_microsecond,hour_second,hour_minute,day_microsecond,day_second,day_minute,day_hour
   日期相关间隔只有年月year_month
3. 如何获取两个日期/时间之间的间隔？
   timediff(),datediff(),timestampdiff(),PERIOD_DIFF() 结果值都是前一个时间减去后一个时间。
4. 更改日期和时间有哪几种方法？
   直接使用+/-符号加上或者减去时间间隔，或者使用函数进行更改
5. 哪些函数可以用来更改日期？
   • adddate,subdate,date_add,date_sub
     区别：adddate和subdate可以直接给一个数字表示增加或减少天数。也可以使用时间间隔来指定具体的单位
     date_add和date_sub必须使用时间间隔指定具体单位
   • adddate(date,days)/adddate(date,INTERVAL expr unit)/date_add(date,INTERVAL expr unit)
   • period_add(P,M) 增加月份
6. 哪些函数可以用来更改时间？
   addtime,subtime
7. 哪些函数可以用来更改日期和时间？
   timestamp(expr1,expr2),timestampadd(unit,interval,datetime_expr)