本文问题
- 在MySQL中，时间间隔用什么格式来表示？
- 时间间隔的单位有哪些？分别表示什么意义？
- 如何获取两个日期/时间之间的间隔？
- 更改日期和时间有哪几种方法？
- 哪些函数可以用来更改日期？
- 哪些函数可以用来更改时间？
- 哪些函数可以用来更改日期和时间？
时间间隔
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'
问题答案
- 在MySQL中，时间间隔用什么格式来表示？
INTERVAL expr unitexpr表示数值unit表示expr的单位 - 时间间隔的单位有哪些？分别表示什么意义？
单一单位：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 - 如何获取两个日期/时间之间的间隔？
timediff(),datediff(),timestampdiff(),PERIOD_DIFF()结果值都是前一个时间减去后一个时间。 - 更改日期和时间有哪几种方法？
直接使用+/-符号加上或者减去时间间隔，或者使用函数进行更改 - 哪些函数可以用来更改日期？
-
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)增加月份
-
- 哪些函数可以用来更改时间？
addtime,subtime - 哪些函数可以用来更改日期和时间？
timestamp(expr1,expr2),timestampadd(unit,interval,datetime_expr)
网友评论