Help Center> GaussDB> Distributed_8.x> SQL Reference> Expressions> Time Interval Expressions

Updated on 2024-06-03 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:

   
        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)