bruce.momjian's picture
Author: Bruce Momjian
10/3/2017

If you often use the timestamp data type, you might not be making full use of it.

In these queries:

CREATE TABLE tztest (x TIMESTAMP);

INSERT INTO tztest VALUES (CURRENT_TIMESTAMP);

SELECT * FROM tztest;
             x
----------------------------
2016-10-25 18:49:20.220891

SHOW timezone;
  TimeZone
------------
US/Eastern

SET timezone = 'Asia/Tokyo';

SELECT * FROM tztest;
             x
----------------------------
2016-10-25 18:49:20.220891

Notice that the time has not changed even though the session time zone has changed. A timestamp date type specification defaults to timestamp without time zone:, to match the sql specification. Contrast the above output with using timestamp with time zone:
 

CREATE TABLE tztest2 (x TIMESTAMP WITH TIME ZONE);

INSERT INTO tztest2 VALUES (CURRENT_TIMESTAMP);

SELECT * FROM tztest2;
               x
-------------------------------
2016-10-25 18:57:04.096335-04

SHOW timezone;
  TimeZone
------------
US/Eastern

SET timezone = 'Asia/Tokyo';

SELECT * FROM tztest2;
               x
-------------------------------
2016-10-26 07:57:04.096335+09

 

Notice that when the session time zone changed the date and hours were adjusted and the suffix time zone offset changed. This allows users from multiple time zones to insert into the same table and for viewers to see all rows in their local time zone.

Timestamp with time zone is implemented by adjusting all values to utc, meaning there is no storage of the original time zone name or its offset. If you need to record the original time zone offset, you can store the output of SELECT EXTRACT(timezone FROM CURRENT_TIMESTAMP) in a separate column.

Bruce Momjian is a Senior Data Architect at EnterpriseDB. 

This post originally appeared on Bruce's personal blog