跳到主要内容

DATE_SUB

声明​

DATE_SUB(date,INTERVAL expr unit)

说明​

返回日期时间 date 减去 expr 后的值。

  • date 表示日期时间基准，可以是 DATE 类型、TIME 类型或 DATETIME 类型。

  • 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_SUB(NOW(), INTERVAL 5 DAY),
     DATE_SUB('2014-01-10', INTERVAL 5 MICROSECOND),
     DATE_SUB('2014-01-10', INTERVAL 5 SECOND),
     DATE_SUB('2014-01-10', INTERVAL 5 MINUTE),
     DATE_SUB('2014-01-10', INTERVAL 5 HOUR),
     DATE_SUB('2014-01-10', INTERVAL 5 DAY),
     DATE_SUB('2014-01-10', INTERVAL 5 WEEK),
     DATE_SUB('2014-01-10', INTERVAL 5 MONTH),
     DATE_SUB('2014-01-10', INTERVAL 5 QUARTER),
     DATE_SUB('2014-01-10', INTERVAL 5 YEAR),
     DATE_SUB('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND),
     DATE_SUB('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND),
     DATE_SUB('2014-01-10', INTERVAL '05:05' MINUTE_SECOND),
     DATE_SUB('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND),
     DATE_SUB('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND),
     DATE_SUB('2014-01-10', INTERVAL '05:05' HOUR_MINUTE),
     DATE_SUB('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND),
     DATE_SUB('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND),
     DATE_SUB('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE),
     DATE_SUB('2014-01-10', INTERVAL '01 05' DAY_HOUR),
     DATE_SUB('2014-01-10', INTERVAL '1-01' YEAR_MONTH)
     \G
*************************** 1. row ***************************
                                      DATE_SUB(NOW(), INTERVAL 5 DAY): 2025-12-12 16:23:56
                       DATE_SUB('2014-01-10', INTERVAL 5 MICROSECOND): 2014-01-09 23:59:59.999995
                            DATE_SUB('2014-01-10', INTERVAL 5 SECOND): 2014-01-09 23:59:55
                            DATE_SUB('2014-01-10', INTERVAL 5 MINUTE): 2014-01-09 23:55:00
                              DATE_SUB('2014-01-10', INTERVAL 5 HOUR): 2014-01-09 19:00:00
                               DATE_SUB('2014-01-10', INTERVAL 5 DAY): 2014-01-05
                              DATE_SUB('2014-01-10', INTERVAL 5 WEEK): 2013-12-06
                             DATE_SUB('2014-01-10', INTERVAL 5 MONTH): 2013-08-10
                           DATE_SUB('2014-01-10', INTERVAL 5 QUARTER): 2012-10-10
                              DATE_SUB('2014-01-10', INTERVAL 5 YEAR): 2009-01-10
       DATE_SUB('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND): 2014-01-09 23:59:54.999995
   DATE_SUB('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND): 2014-01-09 23:54:54.999995
               DATE_SUB('2014-01-10', INTERVAL '05:05' MINUTE_SECOND): 2014-01-09 23:54:55
  DATE_SUB('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND): 2014-01-09 18:54:54.999995
              DATE_SUB('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND): 2014-01-09 18:54:55
                 DATE_SUB('2014-01-10', INTERVAL '05:05' HOUR_MINUTE): 2014-01-09 18:55:00
DATE_SUB('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND): 2014-01-08 18:54:54.999995
            DATE_SUB('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND): 2014-01-08 18:54:55
               DATE_SUB('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE): 2014-01-08 18:55:00
                    DATE_SUB('2014-01-10', INTERVAL '01 05' DAY_HOUR): 2014-01-08 19:00:00
                   DATE_SUB('2014-01-10', INTERVAL '1-01' YEAR_MONTH): 2012-12-10
1 row in set (0.002 sec)

Contents

编辑此页