Thursday, May 31, 2018

timestamptz is easier to adjust than timestamp

x=# create table z(id int primary key, _timestamp timestamp, _timestamptz timestamptz);
CREATE TABLE
x=# 
x=# insert into z(id, _timestamp, _timestamptz) values (1, '2018-05-04T17:37:00Z', '2018-05-04T17:37:00Z');
INSERT 0 1
x=# 
x=# set timezone to 'Asia/Manila';
SET
x=# select 
x-#   _timestamp, 
x-#  _timestamptz, 
x-#  _timestamp at time zone 'UTC' at time zone 'Japan' as "timestamp in Japan", 
x-#  _timestamptz at time zone 'Asia/Tokyo' "timestamp in Japan",
x-#  _timestamp at time zone 'UTC' at time zone 'Asia/Manila' "timestamptz in Philippines", 
x-#  _timestamptz at time zone 'Asia/Manila' as "timestamptz in Philippines"
x-# from z;
-[ RECORD 1 ]--------------+-----------------------
_timestamp                 | 2018-05-04 17:37:00
_timestamptz               | 2018-05-05 01:37:00+08
timestamp in Japan         | 2018-05-05 02:37:00
timestamp in Japan         | 2018-05-05 02:37:00
timestamptz in Philippines | 2018-05-05 01:37:00
timestamptz in Philippines | 2018-05-05 01:37:00

x=# 
x=# set timezone to 'Asia/Tokyo';
SET
x=# select 
x-#   _timestamp, 
x-#  _timestamptz, 
x-#  _timestamp at time zone 'UTC' at time zone 'Japan' as "timestamp in Japan", 
x-#  _timestamptz at time zone 'Asia/Tokyo' "timestamp in Japan",
x-#  _timestamp at time zone 'UTC' at time zone 'Asia/Manila' "timestamptz in Philippines", 
x-#  _timestamptz at time zone 'Asia/Manila' as "timestamptz in Philippines"
x-# from z;
-[ RECORD 1 ]--------------+-----------------------
_timestamp                 | 2018-05-04 17:37:00
_timestamptz               | 2018-05-05 02:37:00+09
timestamp in Japan         | 2018-05-05 02:37:00
timestamp in Japan         | 2018-05-05 02:37:00
timestamptz in Philippines | 2018-05-05 01:37:00
timestamptz in Philippines | 2018-05-05 01:37:00

No comments:

Post a Comment