- https://stackoverflow.com/a/9576170/1442961
- https://dba.stackexchange.com/q/59006/134391
timestamp
meanstimestamp without time zone
, SQL spec, 6.1, point 35:
- If is not specified, then WITHOUT TIME ZONE is implicit
Timestamp & Timestamp with time zone have different behaviors on how they parse input, and how they interact with the session timezone.
psql
psql (9.6.2)
Type "help" for help.
irashid=# create table ts_test(
no_tz timestamp,
with_tz timestamp with time zone
);
CREATE TABLE
irashid=# insert into ts_test values ('2017-05-24 12:00:00', '2017-05-24 12:00:00');
INSERT 0 1
irashid=# insert into ts_test values ('2017-05-24 12:00:00+4', '2017-05-24 12:00:00+4');
INSERT 0 1
irashid=# select * from ts_test;
no_tz | with_tz
---------------------+------------------------
2017-05-24 12:00:00 | 2017-05-24 12:00:00-05
2017-05-24 12:00:00 | 2017-05-24 03:00:00-05
(2 rows)
irashid=# show timezone;
TimeZone
------------
US/Central
(1 row)
irashid=# SET TIME ZONE 'Europe/Berlin'
irashid-# ;
SET
irashid=# select * from ts_test;
no_tz | with_tz
---------------------+------------------------
2017-05-24 12:00:00 | 2017-05-24 19:00:00+02
2017-05-24 12:00:00 | 2017-05-24 10:00:00+02
(2 rows)
Perhaps most elucidating is the difference in the notion of equality of these types
timestamp
(akatimestamp without time zone
) ignores the timezone when considering equality.timestamp with time zone
only care about the instant in time represented. The time zones don't have to be the same for the values to be equal; only the instant in time.
irashid=# create table raw_ts_strings (str varchar);
irashid=# insert into raw_ts_strings values
('2017-05-24 12:00:00+6'),
('2017-05-24 10:00:00+4'),
('2017-05-24 12:00:00+4');
INSERT 0 3
irashid=# select
a.str as a,
b.str as b,
(cast (a.str as timestamp) = cast (b.str as timestamp)) as timestamp_equality,
(cast (a.str as timestamp with time zone) = cast (b.str as timestamp with time zone)) as timstamp_with_tz_equality
from raw_ts_strings as a cross join raw_ts_strings as b
where a.str < b.str -- just to limit duplicates & obvious self-equality.
;
a | b | timestamp_equality | timstamp_with_tz_equality
-----------------------+-----------------------+--------------------+---------------------------
2017-05-24 10:00:00+4 | 2017-05-24 12:00:00+6 | f | t
2017-05-24 10:00:00+4 | 2017-05-24 12:00:00+4 | f | f
2017-05-24 12:00:00+4 | 2017-05-24 12:00:00+6 | t | f
(3 rows)
-- nothing special about "-5", just what timezone happens to be, putting it here for repeatability
irashid=# SET TIME ZONE -5;
irashid=# insert into w_tz values
irashid-# ('2017-05-24 10:00:00-5'),
irashid-# ('2017-05-24 10:00:00-6');
INSERT 0 2
irashid=# insert into wout_tz values
irashid-# ('2017-05-24 10:00:00'),
irashid-# ('2017-05-24 11:00:00');
INSERT 0 2
irashid=# select * from w_tz;
ts_w_tz
------------------------
2017-05-24 10:00:00-05
2017-05-24 11:00:00-05
(2 rows)
irashid=# select * from wout_tz;
ts_wout_tz
---------------------
2017-05-24 10:00:00
2017-05-24 11:00:00
(2 rows)
irashid=# select w_tz.ts_w_tz, wout_tz.ts_wout_tz from w_tz join wout_tz on (w_tz.ts_w_tz = wout_tz.ts_wout_tz);
ts_w_tz | ts_wout_tz
------------------------+---------------------
2017-05-24 10:00:00-05 | 2017-05-24 10:00:00
2017-05-24 11:00:00-05 | 2017-05-24 11:00:00
(2 rows)
-- if we change the timezone, the "w_tz" values display differently, so different join:
irashid=# SET TIME ZONE -6;
irashid=# select * from w_tz;
ts_w_tz
------------------------
2017-05-24 09:00:00-06
2017-05-24 10:00:00-06
(2 rows)
irashid=# select w_tz.ts_w_tz, wout_tz.ts_wout_tz from w_tz join wout_tz on (w_tz.ts_w_tz = wout_tz.ts_wout_tz);
ts_w_tz | ts_wout_tz
------------------------+---------------------
2017-05-24 10:00:00-06 | 2017-05-24 10:00:00
(1 row)
TODO include some examples of times that do not exist in session timezone due to DST