I’m using QuickSight’s new timezone conversion feature in a analysis/dashboard. Unfortunately, it behaves oddly.
My database contains datetime in UTC, for example 14:00 (equivalent to 15:00 in Europe/Berlin time). With timezone conversion off, the analysis/dashboards shows 14:00, perfect. When I turn on timezone conversion and set it to “Europe/Berlin” time (UTC+1), the analysis/dashboard shows 13:00. Rather than calculating UTC+1, QuickSight calculates UTC-1. Summer/Winter time cannot be the issue imho as it would only offset by 1 hour, not 2.
Welcome to QuickSight Community! thank you for posting the question and for letting us knoe.
I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case.
Hope this helps
Thanks for your reply! I’m not able to open a case and I’m not aware that we have a service plan. I’m facing a bug in a service we pay for. Is there another way to report it?
Hi frowin, this happened to me today and after few hours of struggling, I found that the dataset I am using was accidentally switched from ‘SPICE’ to ‘Direct Query’ mode. After switching back to ‘SPICE’ mode, the timezone calculation went back to normal. Hope it helps…
Hi @frowin,
It’s been awhile since we last heard from you. Are you still having issues with your initial request or were you able to find a work around from Mike’s solution?
If we do not hear back within the next 3 business days, I’ll assume that his solution worked and mark as such. If you have any additional questions though, feel free to create a new topic for discussion.
thank you very much for your posts! I didn’t try SPICE yet as we have to use Direct Query. Thanks to @mikeyoung for pointing that difference out! I will try if it works in SPICE correctly, however, we managed timezones in the custom query now.
@duncan is it by intention that timezone conversion is not working in “Direct Query” mode? It’s very confusing as it performs a conversion of some sort, just the wrong one. If it’s SPICE only, maybe one should deactivate the function or make a warning in the documentation or QuickSight itself.
Again thanks a lot for your answers, I appreciate it!
Hello @frowin, the only impact of running a timezone conversion on a dataset using Direct Query should be load times. It could increase the time to load data in an analysis. That makes me think that the issue could be coming from something else.
My first question is, is your datasource coming from one of these database engines?
Timestream
OpenSearch Service
Teradata
SqlServer
The documentation specifies that the functionality does not work with these 4 datasource types. Another possible issue could be the format of your date-time field from your database. For the functionality to run correctly, all datetime columns in a dataset must be normalized to UTC. I’d imagine that could also be causing an issue on the conversion within QuickSight.
I think you built the best work-around option though. I usually just run my timezone conversions through SQL when building a dataset, just so I know how the functionality works. I just wanted to provide some options to try and debug this issue because it should still function in Direct Query.
I’ll mark my response as the solution, but please let me and @duncan know if you have any remaining questions. Thank you!