3 Replies Latest reply on Sep 8, 2012 10:13 AM by Robert Morton

    Postgres + Timezones w/ relative date filters

    Seth Bridges

      Apologies if I am missing something simple here, but I am unable to understand how to get Tableau to work correctly (ie query the DB wrt the timezone of my Tableau instance) when running queries with relative date filters set to 'Today'.

       

      Here's what I have going:

       

      * My Tableau Desktop is running on an XP machine with the timezone set to Pacific time.

      * My Postgres server is running on an FC16 box with localtime set to UTC

      * my postgres database has its timezone also set to UTC.

      * the table that I am querying has the timestamp data (i have tried with col types TIMESTAMP WITH TIME ZONE and WITHOUT TIMEZONE

      * a simple worksheet that counts the IDs of the table with a relative filter of today on my timestamp column.

       

      My issue is that no data comes back from the table after 5pm PDT (ie, the query filter is asking for data in UTC, not local time)

       

      When I look in the Tableau logs, I see a couple of curious things:

       

      1. The connection timezone is being set to UTC even though my windows box is in pacific.

       

      <QUERY protocol='02d98098'>

      SET TIMEZONE TO 'UTC'

      </QUERY>

       

      2. The query being sent to the database has the 'now' time filter being sent in UTC (it is 2012-09-07 22:07 PDT as I write):

       

      (DATE_TRUNC( 'DAY', CAST((TIMESTAMP '2012-09-08 05:07:42.093000') AS TIMESTAMP) ) + 0 * INTERVAL '1 DAY'))

       

      From what I've read of the documentation and forums, I would expect different behavior.

       

      So, am I missing something? Is this misconfiguration, user error, bug?

       

      Any insights appreciated.

       

      Thanks,

      -sb

        • 1. Re: Postgres + Timezones w/ relative date filters
          Robert Morton

          Hi Seth,

           

          The PostgreSQL driver adjusts timestamp values with  time zones based on the client machine's timezone, which is a bit unusual. Since two Tableau Desktop users may reside in different time zones, we have to ensure that they will see the same data when working with the same workbooks. This is especially important when handling extracts with scheduled refreshes, since they may be published from a Desktop instance in a different time zone than Server.

           

          Furthermore, Tableau tries to issue relative date/time filters with respect to the database server system time. This makes the queries more robust to time changes such as daylight savings time, which aren't universally honored. Your relative date filter for 'today' shows zero records at 5 PM because the server system time in UTC has rolled over to a new day (PDT + 7 hours = UTC).

           

          As a workaround, consider creating a calculated field called [Timestamp (PDT)] based on your date/time field, as follows:

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

           

          I hope this helps.

          -Robert

          • 2. Re: Postgres + Timezones w/ relative date filters
            Seth Bridges

            Hi Robert --

             

            I had tried doing that calculation directly in a view on top of my table, and it does the right thing in that it brings all the data back as PDT regardless of where folks are sitting.

             

            However, the issue, I believe is the other (right) side of the filter:

             

            WHERE ((("my_table"."my_date_utc" AT TIME ZONE 'PDT')

             

            >=

             

            (DATE_TRUNC( 'DAY', CAST((TIMESTAMP '2012-09-08 16:29:55.507000') AS TIMESTAMP) ) + 0 * INTERVAL '1 DAY'))

             

            Because DATE_TRUNC('DAY',[col]) is used (by Tableau, I'm assuming) with  UTC timestamp, as soon as UTC midnight rolls around, the value that is being compared against will flop to the next day, regardless of the way I cast my side of the filter.  How can I get the Tableau side of the compare to read:

             

            date_trunc('day',CAST((TIMESTAMP WITH TIME ZONE '2012-09-09 03:29:55.507000') as timestamp with time zone) at time zone 'PDT');

             

            Thanks,

             

            -sb

            • 3. Re: Postgres + Timezones w/ relative date filters
              Robert Morton

              Hi Seth,

              In this case you may not be able to use the relative date filter UI. Instead, create a calculated field which determines the elapsed days (DATEDIFF('day',...)) and have it compensate for time zone differences. This isn't as elegant, but it should serve as a workaround if you only need the data relative to 'today' in PDT.

              -Robert