Conversion between timestamp and timestamptz

savioceg · March 2019

Hi,
I have a table A with a timestamp field ( timestamp) and a table B with timestamp timezone ( timestamptz) , i am performing a join between them both and see a difference of 4 hours. i was wondering if there is a way to explicitly convert the timestamp field to timestamptx so that my join is accurate.

Jim_Knicely · March 2019

Maybe convert the TIMESTAMP to the TIME ZONE in question?

Example:

dbadmin=> CREATE TABLE A (c1 TIMESTAMP);
CREATE TABLE

dbadmin=> CREATE TABLE B (c1 TIMESTAMP WITH TIMEZONE);
CREATE TABLE

dbadmin=> INSERT INTO A SELECT sysdate;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO B SELECT c1 AT TIME ZONE 'PST' FROM A;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;

dbadmin=> SELECT * FROM a;
             c1
----------------------------
 2019-03-25 09:07:33.695176
(1 row)

dbadmin=> SELECT * FROM b;
              c1
-------------------------------
 2019-03-25 13:07:33.695176-04
(1 row)

dbadmin=> SELECT COUNT(*) FROM a JOIN b ON b.c1 = a.c1;
 COUNT
-------
     0
(1 row)

dbadmin=> SELECT COUNT(*) FROM a JOIN b ON b.c1 = a.c1 AT TIMEZONE 'PST';
 COUNT
-------
     1
(1 row)

We're Moving!

Create My New Community Account Now

Conversion between timestamp and timestamptz

Comments

Leave a Comment