1 Reply Latest reply on Oct 3, 2016 11:12 AM by Andrew Macey

    Confused over timestamps in Tableau Server

    Toby Erkson

      Note

      Please read this but do NOT try to solve it, just read it all the way through.  The solution is at the end and will only make sense if you read through this.  I'm sure some of you may figure out the issue right away so to you.

       

       

      Tableau Server stores timestamps in the UTC time zone.  No problem.  So if my server is physically located in the Pacific time zone but saves in UTC then why is there a 5 hour difference instead of a 7 hour difference?

       

      The SQL for the above fields:

      SELECT 
        current_setting('TIMEZONE') AS "_TIMEZONE",
        transaction_timestamp()::TIMESTAMP AT TIME ZONE 'UTC' AS "_db_time_UTC",
        transaction_timestamp()::TIMESTAMP AS "_db_time_plain",
        transaction_timestamp()::TIMESTAMP AT TIME ZONE 'PDT' AS "_db_time_PDT",
        transaction_timestamp()::TIMESTAMP AT TIME ZONE 'PST' AS "_db_time_PST",
      ...
      

       

      For reference using Pacific Standard Time (PST, -8 hours difference):

       

       

      Say what?

      but UTC is this (screen shots taken at slightly different times...it's the HOURS that are important in this conversation):

       

      It wasn't until I saw this page that I realized the difference was PM vs. AM!

       

      I just spent about 2 hours trying to figure out what I was doing wrong, if I had a function or calculation wrong, wondering if my server wasn't located in our new data center as promised, etc.  I wrote this post up and during the investigation I realized my time-of-day period was what I forgetting to consider...O...M...G...!

      Image result for face palm

       

       

      Have a good Friday everyone, hope this made you laugh