Date/time operators

Syntax

[ + | – | * | / ]

Parameters

+  Addition
–  Subtraction
*  Multiplication
/  Division

Notes

  • The operators described below that take TIME or TIMESTAMP inputs actually come in two variants: one that takes TIME WITH TIME ZONE or TIMESTAMP WITH TIME ZONE, and one that takes TIME WITHOUT TIME ZONE or TIMESTAMP WITHOUT TIME ZONE. 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 such 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'