Conversion between timestamp and timestamptz

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.

Comments

  • Jim_KnicelyJim_Knicely - Select Field - Administrator

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file