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.
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');
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.