UTC to local Date conversions in data sets with joined tables

Question & Answer
,
1

Hi All!

My first question to the Quicksight community! Nice to be here.

I am currently trying to build a data set, from an Aurora/MySQL data source. In this data set, I am left joining a handful of tables to a primary table, that includes a few date fields (stored as datetime in the database), with the date values saved as UTC.

If I use the primary table as is, without any joins, I can successfully convert the date I am interested in, into my local time, using the following formula:

parseDate(formatDate({modified},"yyyy-MM-dd HH:mm:ss",'Australia/Adelaide'),"yyyy-MM-dd HH:mm:ss")

This works as I expect:

image
image720×493 15.6 KB

As soon as I join another table to this primary table, the conversion no longer works and the original UTC date/time is returned:

The join between the two tables is on unrelated fields.

Is this normal behaviour?

Thanks

3

Just a quick update on this one.

I have now tested building the data set using Custom SQL, which includes the joins, so there is only one data source and the UTC conversion works fine. This is my current work around.

Regardless, I would still be able to use the Quicksight functionality in its current form.

Could the behaviour above be considered a bug?

1 Like
4

Datetime conversions are much simpler in custom SQL, also would highly recommend not performing a ton of left joins to a primary table, perhaps building with CTEs would be a bit more efficient?

5

Yes, I have come to that conclusion. While the dataset where I did the calculated field on worked fine, for what ever reason, it still displayed it as UTC in the table (with the wrong offset when I displayed it).

So surfacing it in the SQL query seems to be the path of least resistance (and it displays correctly on the report).

Thanks

1 Like