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'
|