CODEWITHSUNDEEP
sqlpostgresqlsqldatatypes
Vincent
Vincent

Reputation: 8856

PostgreSQL (9.6.5) Converting Between Time Zones Returning Time Stamp Without Time Zone

Let's start with a test table:

CREATE TABLE test
(
    ts_tz timestamp with time zone
);

Let's insert one row of data:

INSERT INTO
    test (ts_tz)
VALUES
    (TIMESTAMP WITH TIME ZONE '2016-08-12 10:22:31.949271-00')
;

Now, let's query this one row:

postgres=# select * from test;
             ts_tz             
-------------------------------
 2016-08-12 03:22:31.949271-07

Ok, this makes sense to me so far. ts_tz is a timestamp with time zone and it's displaying as 03:22 with an offset of -07 because that's what my time zone is.

Now, suppose I want to change the time zone to 'US/Pacific'.

postgres=# select ts_tz AT TIME ZONE 'US/Pacific' from test;
          timezone          
----------------------------
 2016-08-12 03:22:31.949271

But now this returns a TIMESTAMP WITHOUT TIME ZONE.

Why is this? How do I keep it as a TIMESTAMP WITH TIME ZONE?

Upvotes: 0

Views: 193

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51649

This is how AT TIME ZONE construct works - timestamptz becomes timestamp without timezone and vice versa:

https://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT enter image description here

If you want timestamp with zone at some time zone specified use SET, eg:

t=# set timezone to 'EST';
SET
t=#  select * from test;
             ts_tz
-------------------------------
 2016-08-12 05:22:31.949271-05
(1 row)

Upvotes: 1

Related Questions