Multiple TIMEZONES in one analysis!?

Hiii! I currently convert dates to the specific timezones in my queries cause I work with different countries. However, Quicksight’s filters and parameters like “Today” or “Start of this month” are in UTC.
I could have all my data in UTC and use this option:
but I work with different countries.
Thank you so much! This community is super kind and helpful! :smiling_face_with_three_hearts:

Hello @ramoshe, how many different timezones are you working with? I think you would have a couple of options here. The best way to handle this would be to switch from using the filters with the rolling dates and switch to a calculated field with an ifelse statement to calculate the relative dates on various timezones.

You could try something like this:
ifelse(truncDate('MM', {date}) = truncDate('MM', now()) AND extract('DD', {date}) <= extract('DD', now()), {date}, NULL)

If you converted your timezone in the layout, this should handle the filtering for the date. Then, if you wanted more options for the user, like to change the start date to start of last month or start of last year, you could add conditional logic to account for that based on now().

The only alternative I can think of would be to have different dashboards based on the country/timezone and set the viewing permissions by groups in QuickSight. That doesn’t sound like fun though so the first suggestion seems like it is worth a shot. Let me know if that helps and we can try to debug it if you run into any issues!

Hi @DylanM !! I’m working with 2 different timezones for now. The problem is that I’m using several datasets in my analysis and I use a parameter to filter the dates. Is there a way to specify in those parameters the timezone (or just substract the hours) for the “now” or any relative date?

Is it possible to set that relative date not in UTC but based on the IP and where the computer is?

Hello @ramoshe, I think we can build something out to get close to your desired result, but unfortunately, not based on the location of the user. What database are you using as your datasource in QuickSight? My thought is that you can use custom SQL to convert your datetime field to the 2 different time zone fields. This is for Redshift, but it is to demonstrate what I am explaining:

select convert_timezone('PST', date_field) as PST_Date,
convert_timezone('EST', date_field) as EST_Date

Then you could add another control for the user to select from options for the time zone and use a calculated field to choose which date field you would return based on the time zone selected.

Redshift, as well as other databases, also have some form of a current_date function. That could allow you to bring that in using the 2 different time zone convert functions as well. I think we have some options here to handle this, it just unfortunately is not a simple dynamic function that you can implement.

Hello @ramoshe, since we have not heard back with any follow-up questions or more information, I will mark my above response as the solution. If you have any further questions on this issue and need some more support, please let me know what problems you are experiencing and I will help guide you further. Thank you!

Hiii Dylan! Thank you so much! I woudl like to select the TIMEZONE of this dynamic defaults but I think is not possible in Quicksight right now.

1 Like

Hello @ramoshe, you are correct, that is not currently available in the default date functionality. I can tag this as a feature request though for our support team! Thank you for your feedback.

1 Like