Time Zone Adjustments

Most databases (including SPICE) store timestamps in UTC time. If you want to be able to dynamically adjust the time zone of the data displayed in your visuals for different users, we can do this using parameters and calculated fields. This technique will work both for if you want to allow Readers to change the time zone themselves (using a Control), or if you want to specify the time zone programmatically via the URL or via the JS SDK (for instance if you are embedding a dashboard in your application and want to tell QuickSight what time zone to display for that user). You can also use the addDateTime function example below and hard code the number of offset hours if you want to change to a different time zone for all users (don’t need to use parameters then).

First create a new parameter. Choose type ‘Integer’ and ‘Single Value’. You can set a default if you’d like (I recommend it, even if it is just 0 which would make the default UTC time). Click ‘Create’ and then choose ‘Calculated field’ on the next screen (or just create a new calculated field if you’ve left that screen already). We are going to use the addDateTime function to adjust the number of hours offset from UTC time (for instance US Pacific time is -7 in the summer and -8 in the winter, which is another issue in itself). The function should look like this (assuming you named your parameter TZOffset):

addDateTime(${TZOffset},"HH",{your timestamp field})

This is the field you will use in all your visuals as it will update based on the parameter value we set. To test it quickly, make a new visual, choose Table as the visual type, and put both the original timestamp field and this new adjusted timestamp field next to one another. Set the aggregation to Hour or Minute. Assuming we set the default parameter value to be 0 then they should look the same.

Now add this to the end of your URL:

#p.TZOffset=-7

You should see the new adjusted time stamp field adjust to PDT now.

Setting the parameter value via the URL is one way to do it. Another way is to set the value using the JS SDK if you are embedding this dashboard into your application. Assuming your application knows what time zone the user is in, you can set this parameter value as you load the embedded dashboard.

The final way to set the parameter value is using a Control. This is useful if you want to allow the end user (Reader) to change the time zone themselves (you can even set a dynamic default per user using ‘Dynamic Defaults for Parameter Controls’). You can either add a control on the current parameter you created, which will look like an integer they can adjust (can be a text box where they type in a number, or can make a slider with the values -12 to 12), OR you could make a new parameter which is a String and make a control which has values like ‘US Pacific, US Mountain, US Central….’ and then make a new calculated field which uses an ifelse statement to look at those values and assign the appropriate # hours offset to it. The ifelse statement would look something like this:

ifelse(${TimeZoneName}=’US Pacific’, -7, ${TimeZoneName}=’US Mountain’, -6, ${TimeZoneName}=’US Central’, -5,….., null)

Then use this field in the first argument of the addDateTime function rather than the parameter in the other calculated field discussed above.

Note – we did not account for Daylight Savings Time in these examples. If using the Control approach you would need to either edit the ifelse statement twice per year to assign the correct offset number, or add additional logic to say if today is between March X and November Y (US Daylight Savings) then add/subtract X hours, otherwise add/subtract X-1 hours. If using the approach where you set the parameter value for # of offset hours via URL or JS SDK, you will probably have already taken account for that in your application and can just pass the correct number to QuickSight.

Hope this is helpful info for users looking to adjust time zones for different sets of users across the globe!

12 Likes

Hello @Jesse, thanks this is helpful. I could not replicate what you explained above when I wan trying to do the steps. Is it possible to walk through and example with some screenshots? Especially this last portion of the analysis?
"you could make a new parameter which is a String and make a control which has values like ‘US Pacific, US Mountain, US Central….’ and then make a new calculated field which uses an ifelse statement to look at those values and assign the appropriate # hours offset to it. The ifelse statement would look something like this:

ifelse(${TimeZoneName}=’US Pacific’, -7, ${TimeZoneName}=’US Mountain’, -6, ${TimeZoneName}=’US Central’, -5,……, null)"

@Ravi_Gor Hi Ravi - check out this example in DemoCentral. You can open the Analysis view from the left menu and then open the calculated fields to see how it is built.
https://democentral.learnquicksight.online/#Dashboard-TipsAndTricks-Calculation-TimeZone-Conversion

FYI we will have ability for the Author to set the timezone of the dashboard coming soon. This first iteration will not support the Readers selecting their own time zone, rather the Author can pre-define it for all users.

2 Likes

This is perfect. Thank you so much for your help. Works great!

2 Likes

When is this future going to be live?

1 Like

Please support this as a standard feature. I strongly want this to embed the QS dashboards to our Saas application. Local time zone if preferable for non engineers.

Hi @Daniel15 @johnnyShippio @Ravi_Gor - this is scheduled to launch end of this week. Take a peek in your QS account next week. It does take some time for features to propagate across all AWS regions, but I expect it should be there by end of next week. To be clear, this is the ability for authors to set the time zone of the entire dashboard (reader do not set their own timezones). You will see the option in the menu Sheets->Layout Settings in the new Analysis UI which is also launching this week :slight_smile:

Hi @Jesse @Ravi_Gor @Daniel15 @johnnyShippio, we are now supporting custom time zone as an out-of-box feature for QS. Please try and let us know if any feedbacks. Thanks! QuickSight launches Custom Time Zone

1 Like