Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Reference/ Expressions/ Time Interval Expressions
Updated on 2025-08-19 GMT+08:00
View PDF

Time Interval Expressions

Syntax: INTERVAL EXPR UNIT

Description: EXPR indicates a value, and the UNIT specifier indicates a unit of the value, such as HOUR, DAY, and WEEK. The keyword INTERVAL and specifier are case insensitive.

Table 1 describes the value range of UNIT in a time interval expression. Any punctuation-separated EXPR format is allowed. The recommended separators are displayed in Table 1.

The INTERVAL expression supports the preceding functions only when sql_compatibility is set to 'MYSQL', b_format_version is set to 5.7, and b_format_dev_version is set to s1.

Table 1 Value range of UNIT in a time interval expression

UNIT Value Range

Expected EXPR Format

MICROSECOND

MICROSECONDS

SECOND

SECONDS

MINUTE

MINUTES

HOUR

HOURS

DAY

DAYS

WEEK

WEEKS

MONTH

MONTHS

QUARTER

QUARTERS

YEAR

YEARS

SECOND_MICROSECOND

'SECOND_MICROSECOND'

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

'YEAR_MONTH'

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
gaussdb=# SELECT DATE_ADD('2018-05-01', INTERVAL 1 DAY);
  date_add  
------------
 2018-05-02
(1 row)

gaussdb=# SELECT DATE_SUB('2018-05-01', INTERVAL 1 YEAR);
  date_sub  
------------
 2017-05-01
(1 row)

gaussdb=# SELECT DATE'2023-01-10' - INTERVAL 1 DAY;
      ?column?       
---------------------
 2023-01-09 00:00:00
(1 row)

gaussdb=#  SELECT DATE'2023-01-10' + INTERVAL 1 MONTH;
      ?column?       
---------------------
 2023-02-10 00:00:00
(1 row)
Parent topic: Expressions