Better Timezone and DST calculations

Almost 4 years ago I posted a piece on converting UNIX timestamps to dates and then to local time where I did all the calculations in Tableau. You can also convert timezones in SQL and it is much easier. As an added bonus it also takes care of half hour timezones!

I have a bunch of dashboards that use the Tableau Workgroup internal repository and they are all kept in UTC. I was looking at a post on The Information Lab on building a better traffic to views dashboard and I noticed that there was no adjusting the time from UTC so my heatmap of peak times was off.

RAWSQL_DATETIME("%1 at time zone 'utc' at time zone %2 " ,[Created At],[Timezone Parameter])

The parameter works fine with a live connection but you might want to just hard code it if you are running extracts. Something like this would work better in extracts:

RAWSQL_DATETIME("%1 at time zone 'utc' at time zone 'America/New_York' " ,[Created At])

To generate the timezone parameter I just ran the following against the repository and put it into a string parameter.

select
name as value,
name || ' (' || abbrev || ')' as display
from pg_timezone_names;

This creates a nice list to display as well as the values to pass in the RAWSQL function.

Overall this should be more performant. I make sure to note in the field name and in the dashboards what timezone the date is in so I might end up with [Created At (GMT)] and [Created At (EDT)] columns.

To roud this back to UNIX timestamps you can combine them to something like:

RAWSQL_DATETIME("(to_date('1970-01-01', 'YYYY-MM-DD') + (%1/ 86400000)) at time zone 'utc' at time zone %2 " ,[Timestamp],[Timezone])