3 Replies Latest reply on Jan 23, 2014 1:45 PM by Randy Scire

    How can tableau read a timestamp with timezone from a PostgreSQL connection?

    Ian Weiner

      Hi!

       

      I am having trouble configuring Tableau and my date/time field, wondering if you could provide some help.  How can tableau read a timestamp with timezone from a PostgreSQL connection?  Tableau is currently showing a different time than my database.  I'm pretty sure Tableau is showing the current Greenwich Mean Time. 

       

      For example, the default example of timestamp with time zone is: "2013-05-29 10:54:21.630381-05"  But Tableau thinks it's now "5/29/2013 3:54:21 PM".  Any way I could configure so it's showing 5 hours behind Greenwhich Mean Time, or Central Time Zone?

       

      Thanks!

       

      Ian

        • 1. Re: How can tableau read a timestamp with timezone from a PostgreSQL connection?
          Robert Morton

          Hi Ian,

           

          Tableau connections to PostgreSQL will request that the database server suppress its behavior of converting timestamps to the client local time zone, since that behavior can lead to inconsistent visualizations for users collaborating from different regions, especially when date/time filters are involved.

           

          You can still retrieve the timestamp data adjusted for a specific time zone by using a RAWSQL calculated field such as the following, which adjusts for Pacific Time and handles daylight savings time appropriately:

          RAWSQL_DATETIME("%1 AT TIME ZONE 'PST8PDT'", [datetime field])

           

          I hope this helps,

          Robert

          • 3. Re: How can tableau read a timestamp with timezone from a PostgreSQL connection?
            Randy Scire

            Building on Robert's suggestion, you can even create a user controlled parameter named [Time Zone] (i.e with values like "EST5EDT", "PST8PDT", etc.) to dynamically drive a "Time Zone Offset" calculated field (TZ_OFFSET) and then use that TZ_OFFSET calculated field to offset your other date time fields in other calculations. This would allow the user to select their time zone from the parameter and it would automatically recalculate the date times shown in the viz that utilize the TZ_OFFSET in their calculations.

             

            Here is the calculation for TZ_OFFSET that leverages Robert's suggestion and a user control parameter [Time Zone]. In this case it will return the number of hours offset from the Time Zone the user chooses:

             

            DATEDIFF('hour', RAWSQL_DATETIME("%1 AT TIME ZONE %2", DATETIME("January 1, 1900 00:00:00"),[Time Zone]), DATETIME("January 1, 1900 00:00:00"))