How to Use Custom Time Zone in Quick Sight Without Shifting Date-Only Fields

Learn Technical Articles
1

How to Use Custom Time Zone in Quick Sight Without Shifting Date-Only Fields

If you’ve ever enabled Custom Time Zone in Quick Sight and noticed that your date-only fields are getting shifted by a day, you’re not alone. Quick Sight doesn’t distinguish between date-only and datetime fields. As a result, when a time zone is applied (e.g., UTC to CST) and crosses midnight, your purely date-based fields might be rolled back by one day.

Below is a straightforward workaround:

  1. Convert the date to a string in your dataset

  2. Parse it back to a date in your analysis

This approach ensures your date-only fields don’t get unintentionally shifted. Here’s how:

1. Dataset Calculated Field

Create a calculated field in your dataset to format the date as a string:


c_ds_service_date_str:

// Quick Sight doesn't distinguish between date-only and datetime fields.

// Custom Time Zone applies a time zone shift to all "date" fields, even if there's no time component.

// For a pure date (no time), this can shift the date by one day if UTC -> CST crosses midnight.

// To avoid this incorrect shift, we convert the date to a string here and parse back at the analysis/runtime level.

formatDate({service_date}, 'yyyy-MM-dd')

2. Analysis Calculated Field

Next, create a calculated field in your analysis to parse that string back into a date:


c_service_date:

// Convert the string back to a date. By storing it as a string in the dataset,

// we bypassed the unwanted custom time zone shift on a date-only field.

parseDate({c_ds_service_date_str}, 'yyyy-MM-dd')

3. Repeat for All Date-Only Fields

Apply the same pattern to any other date-only fields that appear in your visuals.

(Note: Actual timestamps will still be converted by Custom Time Zone as expected.)

Reference

For more details on working with date and time fields in Quick Sight, check out the official documentation.

Summary

This method effectively keeps Quick Sight from shifting your date-only values when a time zone conversion occurs. By treating pure date fields as strings in the dataset layer and converting them back in the analysis layer, you preserve the original date. This is especially helpful when reporting or visualizing daily metrics without unintended time-based offsets.

Hope this helps! If you have any questions or insights, feel free to share them in the comments.

2 Likes
2

Hi @robdhondt

Thanks for sharing this. While I haven’t run into this issue yet, but I may be working on a client in the US timezone and may hit this issue.

So good to be aware and know a way to handle the issue!

Regards,
Giri

1 Like
3

I’m confused because this note on the official documentation suggests that the custom time zone would not apply to fields that are displayed as Date only, without sub-hour precision:

Datetime displays with a granularity that is lower than hour are converted to the selected time zone. For example, if you set the timezone of an analysis to America/New_York (UTC-04:00), the datetime value Dec.1, 2020 12:00am in UTC+00:00 is converted and displayed as Nov.30, 2020 7:00pm. Daylight Savings Time (DST) is incorporated into the datetime conversion.

I have a table displaying a date field with the format “MMM D, YYYY”, which I believe does not meet the criteria for “a granularity that is lower than hour”, and from this documentation should not be affected by time zone conversion.

Is the documentation wrong (and all datetime displays are affected), or am I somehow not configuring this date display correctly?

I’d really like to use custom time zones, but I don’t want to have to republish all of our date-only columns as strings, since (a) we have a lot of them, (b) I would like to be able to perform date-based calculations, and (c) it would likely impact storage sizes of the SPICE tables.

4

The documentation is wrong, all datetime displays are affected.

I completely understand the pain point here. It is tedious to update all the dates fields. The good news is after you parse the stings back to dates in your analysis you can perform date-based calcs (at runtime) like any other date field. You could also do the calcs before converting to a string if you want them materialized in SPICE (no ops at runtime).

1 Like