Relative date filter on computed field is computing incorrect dates

Question

I have a computed field that creates a date value based off of the following formula:

```
addDateTime(${TimeZone}, 'HH', epochDate(parseInt(hour)))
```

`hour` is a field representing an epoch timestamp, and `TimeZone` is a parameter that is set by a control that represents the integer offset from UTC (-8, +2, etc.).  A date is created from the timestamp, then the hour is adjusted by the offset.  This allows dashboard users to specify their timezone to view dates in their local time.

This field is used as a filter along with a relative date control at the top of the dashboard.  However, when a date range is selected in the filter, it continues to be based on UTC time, completely ignoring the parameter value set for `TimeZone`.  For example, when selecting the "Last 4 hours" option, it displays a time range equal to the last 4 hours from *UTC time*.  If -8 is selected as the offset for Pacific Time, it means that the "last 4 hours" generated by the filter is actually 4 hours into the future and no data is shown!

Is there a workaround that can be used to get around this issue (such as an alternative formula), or is this possibly a quicksight platform bug?

Answer

Hello and good morning,

The issue you're encountering in Amazon QuickSight with the computed field not properly adjusting date ranges based on the TimeZone parameter appears to be related to how QuickSight handles date filtering and time zone conversions. This is not necessarily a bug, but rather a limitation in how QuickSight interprets relative date filters and time zone offsets.

To work around this issue, you can approach the problem with a different strategy:

**1. Adjust Date Range Calculation: **Instead of relying solely on QuickSight's relative date filters, consider adjusting your computed field formula to directly calculate the date range based on the selected TimeZone parameter. For example, if you're trying to get the "Last 4 hours" in the user's local time zone (based on the selected TimeZone), you can calculate the start and end timestamps accordingly.Example formula for calculating the start and end timestamps for "Last 4 hours":

sql
```
addDateTime(${TimeZone}, 'HH', epochDate(currentDatetime()) - 4*3600) as StartTime, addDateTime(${TimeZone}, 'HH', epochDate(currentDatetime())) as EndTime
```
Here, currentDatetime() provides the current UTC timestamp, and addDateTime() adjusts it based on the selected TimeZone. Adjust the - 4*3600 (which is 4 hours in seconds) based on your specific needs.

**2. Apply Custom Date Filters: **2. Instead of relying solely on QuickSight's built-in relative date filters, use custom date filters based on the calculated StartTime and EndTime fields. Create a filter in QuickSight using these fields to filter your data based on the desired time range in the user's local time zone.

**3. Testing and Validation:**After implementing the adjusted approach, thoroughly test your dashboard to ensure that the date ranges are correctly calculated and applied based on the selected TimeZone. Check different time zones and relative date ranges to validate the behavior across different scenarios.

By implementing these adjustments, you'll have more control over how date ranges are calculated and applied within your Amazon QuickSight dashboard, ensuring that users see the data in their local time zone as intended. While QuickSight's native date handling might have limitations in this area, custom calculations and filters can often provide a robust workaround for specific use cases like this.

Ismael Murillo

Relative date filter on computed field is computing incorrect dates

Relevant content