Date/time operators

Vertica supports usage of arithmetic operators on DATE/TIME operands:.

Vertica supports usage of arithmetic operators on DATE/TIME operands:

  • + (addition)
  • - (subtraction)
  • * (multiplication)
  • / (division)

Examples

The operators described below that take TIME or TIMESTAMP input have two variants:

  • Operators that take TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE input.

  • Operators that take TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE input.

For brevity, these variants are not shown separately.

The + and * operators come in commutative pairs—for example, both DATE + INTEGER and INTEGER + DATE. Only one of each pair is shown.

Example Result Type Result
DATE '2001-09-28' + INTEGER '7' DATE '2001-10-05'
DATE '2001-09-28' + INTERVAL '1 HOUR' TIMESTAMP '2001-09-28 01:00:00'
DATE '2001-09-28' + TIME 
'03:00'
 TIMESTAMP '2001-09-28 03:00:00'
INTERVAL '1 DAY' + INTERVAL 
'1 HOUR'
 INTERVAL '1 DAY 01:00:00'
TIMESTAMP '2001-09-28 01:00' 
+ INTERVAL '23 HOURS'
 TIMESTAMP '2001-09-29 00:00:00'
TIME '01:00' + INTERVAL 
'3 HOURS'
 TIME '04:00:00'
- INTERVAL '23 HOURS' INTERVAL '-23:00:00'
DATE '2001-10-01' – DATE 
'2001-09-28'
 INTEGER '3'
DATE '2001-10-01' – INTEGER '7' DATE '2001-09-24'
DATE '2001-09-28' – INTERVAL 
'1 HOUR'
 TIMESTAMP '2001-09-27 23:00:00'
TIME '05:00' – TIME '03:00' INTERVAL '02:00:00'
TIME '05:00'  INTERVAL 
'2 HOURS'
 TIME '03:00:00'
TIMESTAMP '2001-09-28 23:00' 
– INTERVAL '23 HOURS'
 TIMESTAMP '2001-09-28 00:00:00'
INTERVAL '1 DAY' – INTERVAL 
'1 HOUR'
 INTERVAL '1 DAY -01:00:00'
TIMESTAMP '2001-09-29 03:00' 
– TIMESTAMP '2001-09-27 12:00'
 INTERVAL '1 DAY 15:00:00'
900 * INTERVAL '1 SECOND' INTERVAL '00:15:00'
21 * INTERVAL '1 DAY' INTERVAL '21 DAYS'
DOUBLE PRECISION '3.5' 
* INTERVAL '1 HOUR'
INTERVAL '03:30:00'
INTERVAL '1 HOUR' / 
DOUBLE PRECISION '1.5'
 INTERVAL '00:40:00'