Date not in UTC, how to change / convert timezone in the dataset?

Hi all,

I have a DATETIME field which is in EEST timezone but once imported it shows as UTC. Let’s say in the original source it is 11:00:00 EEST, in Quicksight this will be shown as 11:00:00Z, so UTC, with no conversion (it would be 08:00:00Z).
I played a bit with parseDate and formatDate but I cannot find a way to create a calculated field to get the value in UTC unless I manually add / remove hours using addDatetime. I would like to avoid this though since DST would screw things up.

Is there any way to have the field converted properly without having to worry about DST changes?
I was thinking that using formatDate I could create a string with date, time and timezone, then use parseDate, but unfortunately time zone info is not in the list of supported formats.

As of now I cannot change settings on my MySQL instance.

Thank you!

Does this question help answer your question: UTC to local Date conversions in data sets with joined tables - #3 by WranglingData

1 Like

Hello @lillie,
Thank you for chiming in and sorry for the delay, it’s been crazy at work and I gave up on this.
I believe that the issue is most probably in the MySQL DB Timezone settings, in the future we are moving to a new solution and hopefully we will overcome this issue, I can do something similar even not the same way: as in QS the date shows as UTC even though it’s EEST, so if I did something like this:

parseDate(formatDate({CREATED_TIME},"yyyy-MM-dd HH:mm:ss",'Europe/Helsinki'),"yyyy-MM-dd HH:mm:ss")

What happens is that I would get the opposite of what I am looking for.
Say this is the CREATED_TIME:

2022-05-13T14:18:33.000Z

This means it’s been created at 14:18 EEST / 11:18 UTC.
What I get using that function is now:

2022-05-13T17:18:33.000Z

What it should become instead is either:

2022-05-13T11:18:33.000Z

Which is the UTC time for that field, or:

2022-05-13T14:18:33.000+03

Whose suffix “+03” indicates the time zone (hoping I didn’t get it wrong).
To get the first result I can use a formula like the above but selecting a -03 timezone, such as:

parseDate(formatDate({CREATED_TIME},“yyyy-MM-dd HH:mm:ss”,‘America/Argentina/Buenos_Aires’),“yyyy-MM-dd HH:mm:ss”)

Not sure though if there could be issues with DST.

Again, this is most probably an issue with the timezone settings in the DB, but it’d have been great to have an option in QuickSight to specify “apply this timezone for this data source”.

Thank you again for replying :slight_smile:

Hi @Massi and @lillie,

We are now supporting custom time zone (QuickSight launches Custom Time Zone) as an out-of-box feature for QS. Please try and let us know if any feedbacks. Thanks!

1 Like

Hello, just want to clarify, in order to use this feature, i have to convert all the date column from local datetime to UTC first?
Thanks