1 2 Previous Next 15 Replies Latest reply on Jul 13, 2018 8:03 AM by Fergal Dalton

    How do I change time zone?

    dalia cohen

      All of the dates in my database are based on UTC, but I want to change the time zone to US/Pacific. I don't want to just subtract 8 hours, as that doesn't take Daylight Savings into account. Is there a way to do this in Tableau? Previously, I was only writing custom queries and so making the change there, but I am hoping to move to just building out my tables and joins in Tableau itself to increase the speed at which my reports load.

       

      Thanks!

        • 1. Re: How do I change time zone?
          Russell Christopher

          Hi Dalia -

           

          Tableau has no auto-magic goodness around time zone conversion that is stored outside of Tableau. We actually store our own dates internally as UTC and have some non-public code that converts that stuff in reports that we serve up, but it's not something you can leverage yourself.

           

          You'll have to use some sort of DateAdd() or TSQL approach to fix these up. Sorry.

          3 of 3 people found this helpful
          • 2. Re: How do I change time zone?
            dalia cohen

            Bummer. Thanks for the quick response, though!

            • 3. Re: How do I change time zone?
              Keith Helfrich

              So, when dealing with multiple years of historical data that comes with a date-time stamp in UTC, we actually have to build in the logic to adjust the time zones & account for daylight savings on our own ?

               

              Sounds downright terrible.

               

              Thanks!

              Keith Helfrich | Twitter

              Red Headed Step Data

              • 4. Re: How do I change time zone?
                rafael diaz

                Try the following for to convert UTC to DST:

                 

                Step 1:Created a calculated field called "Date(TZ Adjusted)" to Convert the original Date field from UTC to your local time zone.

                DATEADD('hour',-5,[Date] ) //Adjust UTC Db time to EST

                 

                Step 2: Create a 2nd calculated field and call it "Date(DST adjusted)" and apply this to the results of Date(TZ Adjusted):

                 

                //Adjust Date to Daylight Savings Time if the Date falls within that period. DST starts on the 2nd Sunday of March

                // and ends on the the 1st Sunday in Nov.

                IF [Date (TZ Adjusted)] >= DATEADD('hour', 2, (IF DATEPART('weekday', DATEADD('month', 2, DATETRUNC('year', [Date (TZ Adjusted)]))) = 1

                // check to see if March started on a Sunday by changing the date to Jan. 1 and adding 2 months

                THEN DATEADD('month', 2, DATETRUNC('year', [Date (TZ Adjusted)])) + 7

                //If yes, then change the date to March 8 by adding 7

                ELSE DATETRUNC('week', DATEADD('month', 2, DATETRUNC('year', [Date (TZ Adjusted)])) + 13)

                //Otherwise change the day to 13 days after the beginning of the week that March 1 is in

                END)) //set the date to the 2nd Sunday in March and then add 2 hours to the date to make it 2am on the 1st day of DST

                 

                AND

                 

                //Same as above except check for 2am on the 1st Sunday in November

                [Date (TZ Adjusted)] <= DATEADD('hour', 2, (IF DATEPART('weekday', DATEADD('month', 10, DATETRUNC('year', [Date (TZ Adjusted)]))) = 1

                 

                THEN DATEADD('month', 10, DATETRUNC('year', [Date (TZ Adjusted)])) + 7

                 

                ELSE DATETRUNC('week', DATEADD('month', 10, DATETRUNC('year', [Date (TZ Adjusted)])) + 6)

                 

                END))

                 

                THEN DATEADD('hour', 1, [Date (TZ Adjusted)]) //Date >= (2am on 2nd Sun of March so Spring forward 1 hour

                 

                ELSE [Date (TZ Adjusted)] //Date <= (2am on 1st Sun of Nov so Fall back 1 hour (meaning don't adjust)

                 

                END

                =====================

                 

                The only caveat is that I haven't figured out how to make this work if your dashboard uses a parameter control for selecting a date in your dashboard to allow the user to show a status for the selected time period selected in the date parameter. You can't put a calculated field within a date parameter control.

                 

                Hope this helps!

                 

                FYI - This was done using version 8.2

                R

                5 of 5 people found this helpful
                • 5. Re: How do I change time zone?
                  allan.walker.0

                  Does your date have location?  If so, you could use this.

                   

                  http://efele.net/maps/tz/world/

                  • 6. Re: How do I change time zone?
                    Rody Zakovich

                    Going off of Rafael, if you wanted to add more functionality.

                     

                    Create 4 variations of Date(TZ Adjusted) and DATE(DST Adjusted), each accounting for the various Time Zones.

                     

                    Then created a Parameter

                    5-8-2015 12-11-29 PM.png

                    Next create a calculated field based on the parameter.

                     

                    pic pic.png

                     

                    Finally drag that calculated field onto your dashboard and show parameter control. This will allow your users to toggle between Time Zones. Very beneficial if all of your servers are on UTC, but you have locations throughout the US.

                    1 of 1 people found this helpful
                    • 7. Re: How do I change time zone?
                      Keith Helfrich

                      Hi Everyone,

                       

                      I've written a blog post fairly recently, about the various options & considerations when converting timezones with Tableau.  I hope you'll find it helpful.

                       

                      Dates, Times, and Universal Coordination

                       

                      Thanks!

                      Keith Helfrich | Twitter

                      Red Headed Step Data

                      • 8. Re: How do I change time zone?
                        Rod Menken

                        I have taken the formulas from Rafael Diaz and combined them into one single calculated field that can be applied to any UTC date field in the postgres database with some editing.  This avoids creating multiple calculations per date field.  Here is the formula in a new Calculated field "Created At (CTS)":

                         

                         

                        // TO use this formula, edit the   "[Created At (UTS)]"  with what ever the date field name is.

                        // To modify for another time zone, replace the "-6" (CTZ) and the "-5" (CTZ Daylight Savings Time) with the proper values for your time zone.

                         

                        IF DATEADD('hour',-6,[Created At (UTS)])>=

                        //Convert date field from UTC time to CST by subtracting 6 hours, then find the beginning of Daylight Savings Time and the End of Daylight Savings time.

                        //If in the range for Daylight Savings Time Central Time Zone, then subtract 5 hours from UTC time.  Else subtract 6 hours from UTC time during the remainder of the year.

                         

                            //Calculate start of daylight savings time.  Find 2 AM on the second Sunday of March.

                           (IF DATEPART('weekday', DATEADD('month', 2, DATETRUNC('year', DATEADD('hour',-6,[Created At (UTS)]))))=1

                                        //1) Start with UTS time and subtract 6 hours to CST.

                                        //2) Truncate to get year 01-01-yyyy of the CTS time zone.

                                        //3) Add 2 months to get March 03-01-yyyy of the CTS time zone.

                                        //4) Find weekday 1-7

                                        //5) If Weekday=1 (Sunday) goto "THEN" otherwise do the "ELSE".

                         

                                THEN DATEADD('day',7,DATEADD('hour',2,DATEADD('month',2,DATETRUNC('year', DATEADD('hour',-6,[Created At (UTS)])))))

                                        //Add 7 Days to get the second Sunday of March, which will be March 8th.  Add 2 Hours to get to 2AM as well.

                         

                                ELSE DATEADD('day',15-DATEPART('weekday', DATEADD('month', 2, DATETRUNC('year', DATEADD('hour',-6,[Created At (UTS)])))),DATEADD('hour',2,DATEADD('month',2,DATETRUNC('year', DATEADD('hour',-6,[Created At (UTS)])))))

                                        //If 1st day of month is not Sunday, then add 15 days and then subtract the Day of the Week (1 thru 7) of the 1st day of the month.  This will get the date of the second Sunday of the month.

                                END)

                         

                        AND

                         

                        DATEADD('hour',-6,[Created At (UTS)])<= 

                         

                            //Calculate end of daylight savings time.  Find 2AM on the first Sunday of November.

                         

                            (IF DATEPART('weekday', DATEADD('month', 10, DATETRUNC('year', DATEADD('hour',-6,[Created At (UTS)]))))=1

                                        //Check to see if 1st day of November is Sunday

                         

                         

                                THEN DATEADD('day',0,DATEADD('hour',2,DATEADD('month',10,DATETRUNC('year', DATEADD('hour',-6,[Created At (UTS)])))))

                                        //If it is Sunday, then add 0 days.

                         

                         

                                ELSE DATEADD('day',8-DATEPART('weekday', DATEADD('month', 10, DATETRUNC('year', DATEADD('hour',-6,[Created At (UTS)])))),DATEADD('hour',2,DATEADD('month',10,DATETRUNC('year', DATEADD('hour',-6,[Created At (UTS)])))))

                                        //If not a Sunday, then the 1st Sunday is + 8 - the Day of the Week (1 thru 7) of the 1st day of the month.

                                END)

                         

                        //********************************************************************************************************************************************************************************************************

                         

                        THEN DATEADD('hour',-5,[Created At (UTS)]) //Date is in the Daylight Savings Time range, so CTS is 5 hours less than UTC time.

                         

                        ELSE DATEADD('hour',-6,[Created At (UTS)]) //Date is outside the Daylight Savings Time range, so CTS is 6 hours less than UTC time.

                         

                        END

                        4 of 4 people found this helpful
                        • 9. Re: How do I change time zone?
                          Rawan Alsiekh

                          Hello all,

                           

                          I found a formula that was posted online by Jonathan (sorry I couldn't find a last name), I had to change few things for it to work appropriately, it is simple and not limited to a specific Date/Time. it is also looking at Date and Weekday for Daylight Saving, here it is:

                           

                          IF DATETIME([Created At (UTS)]) >= DATETIME(STR(YEAR(date([Created At (UTS)]))) + '-03-' +

                              STR(IF DATEPART('weekday', DATE(STR(YEAR(date([Created At (UTS)]))) + '-03-01')) = 1

                              THEN 9

                              ELSE 16

                              END - DATEPART('weekday', DATE(STR(YEAR(date([Created At (UTS)]))) + '-03-01'))) + ' 08:00:00')

                              AND DATETIME([Created At (UTS)]) < DATETIME (STR(YEAR(date([Created At (UTS)]))) + '-11-' +

                              STR(IF DATEPART('weekday', DATE(STR(YEAR(date([Created At (UTS)]))) + '-11-01')) = 1

                              THEN 2

                              ELSE 9

                              END - DATEPART('weekday', DATE(STR(YEAR(date([Created At (UTS)]))) + '-11-01'))) + ' 08:00:00' )

                          THEN DATEADD('hour', -5, DATETIME([Created At (UTS)]))

                          ELSE DATEADD('hour', -6, DATETIME([Created At (UTS)]))

                          END

                           

                          I hope it helps.

                          3 of 3 people found this helpful
                          • 10. Re: How do I change time zone?
                            Nathan Panuco

                            This is a pretty old thread but it showed up in search results when I had this same problem. The suggested fixes seem a bit complex. I was able to achieve the same results using a fairly simple calculated field: DateAdd('hour', -4, [DATE FIELD])

                            • 11. Re: How do I change time zone?
                              Jose Charles

                              Nathan Panuco I still think this formula you mentioned won't fix it as you are not accounting for the DST which is what others are addressing in those complex formula.

                              • 12. Re: How do I change time zone?
                                Jose Charles

                                Worked great for me! Thanks, Rawan!

                                • 13. Re: How do I change time zone?
                                  Rod Menken

                                  Yes, that is why the complex formula, it has to take into daylight savings time.   UTC time does not have daylight savings time, but much of the US does.  So from 2 AM on the 2nd Sunday of March til 2 AM on the first Sunday of November, in the CST zone, it would be 5 hours different from UTC, and the rest of the year it is 6 hours different.  Then multiple this by at least a hundred time fields in Workgroup database, and you have a lot of places where this could have an impact.

                                  • 14. Re: How do I change time zone?
                                    Tam Nguyen

                                    Rawan, thanks for your formula.  The formula works perfectly when using data extract, but it doesn't work when using live connection.  When I troubleshoot, found out that the first part of the formula (calculate for dates greater or equal to second Sunday of March) works with live connection, but the second part (calculate for dates less than first Sunday of November) doesn't work with live connection!  Have any insight why formula doesn't work with live connection?

                                    1 2 Previous Next