I am truly amazed that Tableau cannot convert from UTC to a local per-user time zone. When developing a global product as we do UTC is the only sensible choice to store dates and times, but the inability for Tableau to show dates/times in the users local time zone (as our .net application easily does with 1 line of code) causes us problems.
I have seen numerous solutions to this on this forum and blogs, but have not found a complete solution. Some of the limitations I've come across are:
- Not dealing with summer time
- Dealing with summer time, but for only 1 timezone and with a hard-coded list of years (to work around the difficulty of rules based on 1st Sunday of May, etc)
- Ignoring the fact that the rules for summer time actually change (see Time Zone News: Countries That Change Their Clocks for details of changed rules and new time zones)
- Using external lookup databases
- Using external excel sheets
I have written a tool in .Net that generates a single Tableau formula which I believe will correctly convert any date that .Net can convert (I have noticed that this excludes dates before 2007 as .Net doesn't contain the rules for this). I will convert pre 2007 date/times but I do not know if the correct "rule" will be used. The calculated field includes comments.
This is still an "early" draft so i am not releasing the .net tool and I make no promises regarding the accuracy, but I am sharing now as I hope this can be useful to someone.
I have found that Tableau takes awhile to validate that the formula is valid.
Also included is a list of timezones that if you copy to the clipboard can be pasted into a parameter.
To use the formula make sure you have a parameter called "Time Zone" and find-replace "Your Field Name Here" with the name of your UTC field.
Message was edited by: Andrew Teece - Updated "Tableau UTC Formula.txt" attachment to fix a bug In my haste to post I found that some timezone formula were missing an "Else" branch. This is for scenarios where there used to be a DST rule, but it has been removed. An example of this is “(UTC+09:00) Yakutsk” which is technically called “Russia TZ 8 Standard Time”. They have changed their rule: • Before 2011 “Between 5th Sunday of March at 02:00 and 5th Sunday of October at 03:00 add 01:00:00” • 2011 “Between 5th Sunday of March at 02:00 and 1st Saturday of January at 00:00 add 01:00:00” • 2012 “Between 1 of January at 00:00 and 1 of January at 00:00 add 00:00:00” – So no DST that year. • 2013 “Between 1 of January at 00:00 and 1 of January at 00:00 add 00:00:00” – So no DST that year. • 2014 “Between 1st Wednesday of January at 00:00 and 5th Sunday of October at 02:00 add 01:00:00” • 2015 onwards – no DST Some countries are clearly indecisive on this stuff!