The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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