Display single calendar day on US East timezone?

[conoroneillsb]

I’m struggling with something quite basic in QuickSight. We have data in a Postgres DB with all timestamps as UTC. We’re building a dashboard showing that data for a selectable calendar day using a Control and Parameter.

It obviously all works fine for us in UTC land with no changes. However I’m going around in circles trying to get it to work for people in US-East (who are the intended audience).

Setting the timezone of the Analysis to US-East correctly sets the datetime in the visualisations. But selecting any day gives me 8pm the previous day to 8pm that day. i.e. it is retrieving data from Postgres based on the UTC calendar day not the TZ adjusted one.

I’ve tried some of the older approaches recommended here prior to the Timezone feature being implemented but nothing ever quite works. Either the date-time range is wrong or that actual times shown are wrong.

I’ve also tried returning US-East TZ data from Postgres and disabling the Timezone feature but that makes things even worse.

Is there any way to display a calendar day of US East data in a visualisation? Note that we don’t need selectable timezones. Everyone using the dashboard will be in US East.

Thanks.

[duncan]

Hello @conoroneillsb , welcome to the QuickSight community!

I think your best bet if the timezone feature in QuickSight is not working is doing this in SQL during ingestion from Prostgres. You can use CustomSQL when creating a new dataset.

I would also check out this post:

Automatic TimeZone Conversion Not Working Correctly

We have a report running off a RDS database where the timestamps are in UTC but the data type is “TIMESTAMP WITHOUT TIMEZONE”. When we try and apply Quicksight automatic time zone adjustment, the adjustment is in the wrong “direction”. I.e. given the below times in UTC: [image] We would expect that upon changing the timezone to “Paris” that in the above report “9:16:38” would be adjusted to “11:16:38”, instead, as shown below, the time is adjusted backwards and becomes “7:16:38”. [image] Thi…

[conoroneillsb]

Thanks @duncan - Almost all of our queries use Custom SQL. We’ll move our focus to making the changes there.

On a sidenote - Seems very strange for a product by a US company not to have rock solid handling of US timezones. But I guess timezones are up there with caching and off by one errors

[duncan]

Hey @conoroneillsb

I agree, and you’re not the first person to run into this issue. My team is hoping for an update on this as well, and this a workaround we have used to make sure we get the time zones right.