Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API
Arithmetic and time-zone operators for dates, times, timestamps, and intervals.
The + and * operators have commutative pairs. For example, both date + integer and integer + date are valid.
| Operation | Return Type | Example |
|---|---|---|
date + integer | date | date '2024-03-15' + 7 → 2024-03-22 |
date + interval | timestamp | date '2024-03-15' + interval '1 day' → 2024-03-16 00:00:00 |
date + time | timestamp | date '2024-03-15' + time '10:00:00' → 2024-03-15 10:00:00 |
interval + interval | interval | interval '1 hour' + interval '30 minutes' → 01:30:00 |
timestamp + interval | timestamp | timestamp '2024-03-15 12:00:00' + interval '2 hours' → 2024-03-15 14:00:00 |
time + interval | time | time '12:00:00' + interval '1 hour' → 13:00:00 |
- interval | interval | - interval '1 hour' → -01:00:00 |
date - date | integer | date '2024-03-22' - date '2024-03-15' → 7 |
date - integer | date | date '2024-03-22' - 7 → 2024-03-15 |
date - interval | timestamp | date '2024-03-16' - interval '1 day' → 2024-03-15 00:00:00 |
time - time | interval | time '14:00:00' - time '12:00:00' → 02:00:00 |
time - interval | time | time '13:00:00' - interval '1 hour' → 12:00:00 |
timestamp - interval | timestamp | timestamp '2024-03-15 14:00:00' - interval '2 hours' → 2024-03-15 12:00:00 |
interval - interval | interval | interval '1 hour' - interval '30 minutes' → 00:30:00 |
timestamp - timestamp | interval | timestamp '2024-03-15 14:00:00' - timestamp '2024-03-15 12:00:00' → 02:00:00 |
interval * integer | interval | interval '30 minutes' * 2 → 01:00:00 |
interval * double precision | interval | interval '1 hour' * 1.5 → 01:30:00 |
interval / double precision | interval | interval '1 hour' / 2 → 00:30:00 |
Use the at time zone operator to convert between timestamp and timestamp with time zone values. Specify the time zone as a name or as a UTC offset interval.
| Operation | Return Type | Example |
|---|---|---|
timestamp at time zone 'timezone' | timestamptz | timestamp '2001-08-16 20:38:40' at time zone 'Europe/Berlin' → 2001-08-16 18:38:40+00 (assumes the default time zone is UTC) |
timestamptz at time zone 'timezone' | timestamp | timestamptz '2001-08-16 18:38:40+00' at time zone 'Europe/Berlin' → 2001-08-16 20:38:40 |
timestamp at time zone interval 'interval' | timestamptz | timestamp '2001-08-16 20:38:40' at time zone interval '+2:00' → 2001-08-16 18:38:40+00 (assumes the default time zone is UTC) |
timestamptz at time zone interval 'interval' | timestamp | timestamptz '2001-08-16 18:38:40+00' at time zone interval '+2:00' → 2001-08-16 20:38:40 |
P1DT2H3M4S corresponds to 1 day, 2 hours, 3 minutes, and 4 seconds.EXTRACT(EPOCH FROM ...) on each value and subtract the results. The - operator makes the same adjustments but returns the result as an interval of days, hours, minutes, and seconds.These queries show the difference between the two approaches. The samples use timezone = 'US/Eastern' and span a daylight saving time change.
1SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
2EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
3Result: 10537200
4
5SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
6EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'))
7/ 60 / 60 / 24;
8Result: 121.958333333333
9
10SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
11Result: 121 days 23:00:00
j