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!
