1 Reply Latest reply on Apr 4, 2018 8:17 PM by Manuel Munoz Jr

    Conversion from UTC to user timezone

    Andrew Teece

      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!