Display Date as Local Timzone, not UTC in AWS Quicksight

Question

So reading through the AWS Quicksight docs, I have found the following information.

Handling Date Time Zones:

Amazon QuickSight uses UTC time for querying, filtering, and displaying date data. When date data doesn't specify a time zone, Amazon QuickSight assumes UTC values. When date data does specify a time zone, Amazon QuickSight converts it to display in UTC time. For example, a date field with a time zone offset like 2015-11-01T03:00:00-08:00 is converted to UTC and displayed in Amazon QuickSight as 2015-11-01T15:30:00.

I have a range of dates in my Athena dataset that I am analyzing in Quicksight. I would like to be able to view these dates in Quicksight as the local timezone representation, not as UTC format. Can anyone advise what would be the best approach for this would be or if it is possible at all? It seems if I utilize a calculated field function like formatDate(), or even custom SQL of 'AT TIME ZONE' then my date columns are converted to strings. Then any attempt to convert these strings back to a date simply convert the date back to UTC format.

I have tried converting the returned date string with:

parseDate({NEWDATE}, "yyyy-MM-dd HH:mm:ss.SSS ZZZ", "Australia/Melbourne")

However, this keeps raising an error "This function does not have the correct number of arguments".

Any advice is appreciated.

Answer 1

I've encountered a similar problem. As a workaround (which does not handle DST), you can use the addDateTime function.

For example:

• Calculated filed name: datetimemelbourne
• Formula: addDateTime(11, 'HH', {datetime})

Answer 2

Had the same issue with formatDate returning string and parseDate not supporting SPICE. In the end, the following solution worked well for me.

parseDate(formatDate({DATE}, 'yyyy-MM-dd', 'America/New_York'))

formatDate returns string in the required timezone, while parseDate is converting it back to date. I didn't find a way to keep the time perhaps due to parseDate issues with SPICE but it didn't matter much as I'm concerned with dates.