Time Interval Expressions

Syntax: INTERVAL EXPR UNIT

Description: This is the syntax of the interval_expr expression, representing a time interval. INTERVAL is the keyword. EXPR is any expression and will be interpreted as a value of the time type. UNIT is a specific keyword and represents the unit of a value, such as, HOUR, DAY, or WEEK. The keyword INTERVAL and specifier are case insensitive.

Table 1 describes the value range of UNIT in a time interval expression.

interval_expr can be used as the input parameter of the DATE_ADD(), DATE_SUB(), ADDDATE(), SUBDATE(), and TIMESTAMPADD() functions for calculation together with a numeric value of the time type.
It also can be used with the addition (+) or subtraction (–) operator for calculation together with a numeric value of the time type.
For the subtraction operation, interval_expr is permitted only on the right side because it makes no sense to subtract a time value from an interval.
The time units listed in Table 1 can be classified into single-time units and multi-time units.
- Single-time units range from MICROSECOND to YEAR. With a single-time unit, EXPR is interpreted as a time value having one time unit. For example, '1'YEAR represents one year.
- Multi-time units range from SECOND_MICROSECOND to YEAR_MONTH. With a multi-time unit, EXPR is interpreted to as a time value having multiple time units. For example, with DAY_MINUTE specified, EXPR is interpreted to have three parts including days, hours, and minutes.
Consecutive numeric characters in EXPR will be interpreted as valid numbers and can be separated by any character. For example, '1:1:1' DAY_MINUTE represents 1 day 1 hour 1 minute. If interpreted valid digits do not map to all the time units contained in the multi-time unit, 0s are added to the leftmost parts. For example, '1:1' DAY_MINUTE represents 0 day 1 hour 1 minute.

**Table 1** Value range of UNIT in a time interval expression
UNIT Value Range	Expected EXPR Format
MICROSECOND	MICROSECOND
SECOND	SECOND
MINUTE	MINUTE
HOUR	HOUR
DAY	DAY
WEEK	WEEK
MONTH	MONTH
QUARTER	QUARTER
YEAR	YEAR
SECOND_MICROSECOND	SECOND_MICROSECOND
MINUTE_MICROSECOND	MINUTE_MICROSECOND
MINUTE_SECOND	MINUTE_SECOND
HOUR_MICROSECOND	HOUR_MICROSECOND
HOUR_SECOND	HOUR_SECOND
HOUR_MINUTE	HOUR_MINUTE
DAY_MICROSECOND	DAY_MICROSECOND
DAY_SECOND	DAY_SECOND
DAY_MINUTE	DAY_MINUTE
DAY_HOUR	DAY_HOUR
YEAR_MONTH	YEAR_MONTH

Example:

   
    
      
      
        m_db=# select date_add('1999-12-31',interval '1' day);
  date_add  
------------
 2000-01-01
(1 row)

m_db=# select adddate('1998-12-03', interval '1' YEAR);
  adddate   
------------
 1999-12-03
(1 row)

m_db=# select date_sub('1999-12-31',interval '1' day);
  date_sub  
------------
 1999-12-30
(1 row)

m_db=# select subdate('1998-12-03', interval '1' YEAR);
  subdate   
------------
 1997-12-03
(1 row)

m_db=# select timestampadd(day, '1', '1999-12-31');
 timestampadd 
--------------
 2000-01-01
(1 row)

m_db=# select '1998-12-03' + interval '1' YEAR;
  ?column?  
------------
 1999-12-03
(1 row)

m_db=# select '1998-12-03' - interval '1' YEAR;
  ?column?  
------------
 1997-12-03
(1 row)

m_db=# select interval '1' YEAR + '1998-12-03';
  ?column?  
------------
 1999-12-03
(1 row)

m_db=# select interval '1' YEAR - '1998-12-03';
ERROR:  syntax error at or near "-"
LINE 1: select interval '1' YEAR - '1998-12-03';
                                 ^

       

     

   
  