跳到主要内容

DATE_ADD

声明​

DATE_ADD(date, INTERVAL expr unit)

说明​

DATE_ADD 函数用于向指定日期添加一段时间，并返回计算结果。

参数说明如下：

  • date 表示日期时间基准，可以是日期类型、时间类型与日期时间类型。

  • expr 表示时间间隔，可以为负值。

  • 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_SECOND组合天到秒'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE组合天到分钟'DDAYSD HOURS:MINUTES'
DAY_HOUR组合天到小时'DAYS HOURS'
YEAR_MONTH组合年到月'YEARS-MONTHS'

示例​

SELECT
     DATE_ADD(NOW(), INTERVAL 5 DAY),
     DATE_ADD('2021-01-10', INTERVAL 5 MICROSECOND),
     DATE_ADD('2021-01-10', INTERVAL 5 SECOND),
     DATE_ADD('2021-01-10', INTERVAL 5 MINUTE),
     DATE_ADD('2021-01-10', INTERVAL 5 HOUR),
     DATE_ADD('2021-01-10', INTERVAL 5 DAY),
     DATE_ADD('2021-01-10', INTERVAL 5 WEEK),
     DATE_ADD('2021-01-10', INTERVAL 5 MONTH),
     DATE_ADD('2021-01-10', INTERVAL 5 QUARTER),
     DATE_ADD('2021-01-10', INTERVAL 5 YEAR),
     DATE_ADD('2021-01-10', INTERVAL '5.000005' SECOND_MICROSECOND),
     DATE_ADD('2021-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND),
     DATE_ADD('2021-01-10', INTERVAL '05:05' MINUTE_SECOND),
     DATE_ADD('2021-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND),
     DATE_ADD('2021-01-10', INTERVAL '05:05:05' HOUR_SECOND),
     DATE_ADD('2021-01-10', INTERVAL '05:05' HOUR_MINUTE),
     DATE_ADD('2021-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND),
     DATE_ADD('2021-01-10', INTERVAL '01 05:05:05' DAY_SECOND),
     DATE_ADD('2021-01-10', INTERVAL '01 05:05' DAY_MINUTE),
     DATE_ADD('2021-01-10', INTERVAL '01 05' DAY_HOUR),
     DATE_ADD('2021-01-10', INTERVAL '1-01' YEAR_MONTH)
     \G
*************************** 1. row ***************************
                                      DATE_ADD(NOW(), INTERVAL 5 DAY): 2025-12-22 16:20:13
                       DATE_ADD('2021-01-10', INTERVAL 5 MICROSECOND): 2021-01-10 00:00:00.000005
                            DATE_ADD('2021-01-10', INTERVAL 5 SECOND): 2021-01-10 00:00:05
                            DATE_ADD('2021-01-10', INTERVAL 5 MINUTE): 2021-01-10 00:05:00
                              DATE_ADD('2021-01-10', INTERVAL 5 HOUR): 2021-01-10 05:00:00
                               DATE_ADD('2021-01-10', INTERVAL 5 DAY): 2021-01-15
                              DATE_ADD('2021-01-10', INTERVAL 5 WEEK): 2021-02-14
                             DATE_ADD('2021-01-10', INTERVAL 5 MONTH): 2021-06-10
                           DATE_ADD('2021-01-10', INTERVAL 5 QUARTER): 2022-04-10
                              DATE_ADD('2021-01-10', INTERVAL 5 YEAR): 2026-01-10
       DATE_ADD('2021-01-10', INTERVAL '5.000005' SECOND_MICROSECOND): 2021-01-10 00:00:05.000005
   DATE_ADD('2021-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND): 2021-01-10 00:05:05.000005
               DATE_ADD('2021-01-10', INTERVAL '05:05' MINUTE_SECOND): 2021-01-10 00:05:05
  DATE_ADD('2021-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND): 2021-01-10 05:05:05.000005
              DATE_ADD('2021-01-10', INTERVAL '05:05:05' HOUR_SECOND): 2021-01-10 05:05:05
                 DATE_ADD('2021-01-10', INTERVAL '05:05' HOUR_MINUTE): 2021-01-10 05:05:00
DATE_ADD('2021-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND): 2021-01-11 05:05:05.000005
            DATE_ADD('2021-01-10', INTERVAL '01 05:05:05' DAY_SECOND): 2021-01-11 05:05:05
               DATE_ADD('2021-01-10', INTERVAL '01 05:05' DAY_MINUTE): 2021-01-11 05:05:00
                    DATE_ADD('2021-01-10', INTERVAL '01 05' DAY_HOUR): 2021-01-11 05:00:00
                   DATE_ADD('2021-01-10', INTERVAL '1-01' YEAR_MONTH): 2022-02-10
1 row in set (0.002 sec)

Contents

编辑此页