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.

Cappella

I've read in the docs that 'parseDate is not supported for use with SPICE data sets.'. This is maybe why you are having the issue with the parseDate function. – Yockey

FYI all the date functions (and string functions) are supported in SPICE now – Mistrial

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})

Yockey

Are you still using this workaround? I cant imagine why AWS does not fixed it yet. I'm not feeling myself safe to use this, cause every DST moment it gonna break. – Edlyn

Yes. I haven't seen any update on this by QuickSight yet. – Yockey

Answer 2

This has worked for me:

parseDate(toString(formatDate({your_date_time_field},'MM-dd-yyyy HH:mm:ss','America/New_York')),

'MM-dd-yyyy HH:mm:ss' )

Milium

Answer 3

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.

Louden

Answer 4

You will want to use formatDate, not parseDate. Should look like this:

formatDate({your date field},"MM/dd/yy HH:mm",'America/Los_Angeles')

parseDate is meant for converting strings to dates.

Mistrial

Answer 5

I found a solution that works for this kinda combination of the addDateTime, but with an ifelse()

ifelse(
(
    
    (
        extract("MM", {your_date}) >= 3 
        AND 
        extract("DD", {your_date}) >= 14
    ) OR (
        extract("MM", {your_date}) <= 11
        AND 
        extract("DD", {your_date}) <= 7
    )
), 
addDateTime(-4, "HH", {your_date}),
addDateTime(-5, "HH", {your_date}))

This would be for converting to EST from UTC.

Sitton

Answer 6

We can use below for Daylight Saving Time is observed, between the second Sunday in March and the first Sunday in November.

ifelse( (

(
    extract("MM", {your_date}) >= 3 
    AND 
    extract("DD", {your_date}) >= 7
    AND
    extract("WD", {your_date}) = 1
    
) OR (
    extract("MM", {your_date}) <= 11
    AND 
    extract("DD", {your_date}) <= 7
    AND
    extract("WD", {your_date}) = 1
)

), addDateTime(-7, "HH", {your_date}), addDateTime(-8, "HH", {your_date}))

Carbamate

Answer 7

You can now set a timezone in your sheet and all your dates will be converted from UTC to your timezone. This can be done by going to "Analysis settings" from "Edit" menu. More information here.

However usage of this depends on your datasource. However Timestream for analytics datasource, at the time of writing this answer didn't support the function they were using and it failed. Given that Timestream for analytics uses some version of Athena/Trino behind the scenes to at least handle querying, it might be Athena also not working.

Plexiform