In Dataset Settings there is an option called "HOURS OFFSET". When you have only one time column, it's working as expected: it adds the corresponding number of hours to the time column while outputting it on the chart. However, when you have more than one temporal columns, the number of hours is logically expected to be added to all of them. Now it adds to only the one column that is selected as the "TIME COLUMN" in the chart.

How to reproduce the bug

  1. Create a dataset with more than one temporal field (e.g. Creation and expiration dates, as in the screencast),
  2. Add "HOURS OFFSET" to the dataset settings (e.g. 4) and save the dataset,
  3. Choose a "TIME COLUMN" in the Time settings of the chart and click Update,
  4. Choose another "TIME COLUMN" and click Update

Expected results

X hours (set in "HOURS OFFSET") should be added to all temporal fields, regardless of the "TIME COLUMN" selected.

Actual results

X hours are added to only one column, the one that is selected as the "TIME COLUMN" in the chart.

Note, that in the screencast, the "Expire Date" of the bottom row is actually equal to the "Agreement Creation Date" of the top row, but they are displayed with a 4-hour difference either way, because of the "HOURS OFFSET" option set.

Screenshots

https://user-images.githubusercontent.com/43491361/220833739-32950223-19ba-40e4-be12-d09804c40fde.mp4

Note, that the "Expire Date" of the bottom row is actually equal to the "Agreement Creation Date" of the top row, but they are displayed with a 4-hour difference either way, because of the "HOURS OFFSET" option set.

Environment

(please complete the following information):

  • browser type and version: Version 110.0.5481.112 (Official Build) (64-bit)
  • superset version: latest (checked on latest-dev too)
  • any feature flags active: "DASHBOARD_CROSS_FILTERS": True, "ALERT_REPORTS": True, "ENABLE_TEMPLATE_PROCESSING": True, "ENABLE_TEMPLATE_REMOVE_FILTERS": True, "GENERIC_CHART_AXES": True

Checklist

Make sure to follow these steps before submitting your issue - thank you!

  • [x] I have checked the superset logs for python stacktraces and included it here as text if there are any.
  • [x] I have reproduced the issue with at least the latest released version of superset.
  • [x] I have checked the issue tracker for the same issue and I haven't found one similar.

Comment From: gfieremans

Happens for me as well. After some more investigating.

  • Offset only ever seems to work for the main_dttm_col.
  • A groupBy: [date_time] works and applies the offset (as long as date_time is the default datetime).
  • A groupBy: [{ expressionType: SQL, label: WHEN, sqlExpression: date_time}] does not work - just strips any timezone information offered.

(Note: the groupBy above is the view from the perspective of a chart export).

I usually work with the second variant because that allows me to re-label the column, so what I end up doing is only providing local time from my source (luckily I control my source).

Comment From: rusackas

Is this still an issue in 3.x?

Comment From: saghatelian

Hi @rusackas I'm testing the 4.0.0 version now, and yes, the bug is there. Actually, as there is no "TIME COLUMN" option in the chart, now the hour offset doesn't affect the table chart at all (at least I couldn't make it affect any of the datetime fields). The same bug is present in the dashboard view. As my original video was probably deleted, I just recorded another one, showing the bug on the 4.0.0 version. https://github.com/apache/superset/assets/43491361/af9bf69c-b12f-4dac-88d8-e13c28a1956b

Thank you for your time and effort.

Comment From: saghatelian

Ok, now I see that the original video is there too :) . Somehow It wasn't displayed several hours ago as I was writing the reply.

Comment From: rusackas

Thanks for the video... the link to the video works great. Maybe it's too big to be displayed on the thread or something ¯\_(ツ)_/¯

Anwyay, that is a very interesting bug you found, and I'm not sure what all needs to change for this.

In the Dataset Editor, I see that the "hours offset" field says it will be applied to "the time column" and not "all temporal columns" - I would guess this is in itself problem, applying to "Default datetime" in the dataset rather than all columns marked with "Is temporal".

The filters do tie in to a single column on charts (which is why you can't remove the last temporal filter when editing a chart), but I'm not sure how the "hours offset" is mapped into the query, and why it would be the filtered column instead of all temporal columns.

For that matter, I'm also wondering if the time filter works as expected... does it filter based on the offset time or the base UTC time? Clearly I'm not an "hours offset" power user ;)

Comment From: rusackas

I wonder if @villebro or @michael-s-molina or @john-bodley know more about how this is all wired up... at least to conjecture a bit more ;)

Comment From: lyndsiWilliams

Hi! We're also experiencing this issue on our instance of Superset. I don't have a solution, but I thought I could provide more information to hopefully help figure out what's causing this.

In our example, the dataset has a -4 hour offset and we're using line charts with a time comparison of 1 day ago. The metric line uses a custom SQL query and the X-axis is the default temporal column on the dataset. You can see the original metric line ends at 16:00 but the "1 day ago" comparison line ends at 12:00.

A strange observation I noticed: The original metric line has the correct -4 hours offset. The "1 day ago" comparison line seems to have an additional -4 hours offset applied. It seems that the hours offset might be applied twice within this functionality somewhere.

I hope this helps, happy to continue discussing if anyone has any ideas for a solution! 💡

https://github.com/apache/superset/assets/55605634/002ccc3e-2da0-4b6d-9c19-b5d362b67bcf

Comment From: rusackas

Hey all... it's been upward of a year since this was touched. Is this still happening in 4.1.1 or 5.0.0rc1? Volunteers encouraged to provide either updated context or a PR/fix, before this gets closed as stale. Sounds potentially important though, so I hope someone can show it some love.

Comment From: saghatelian

Hi @rusackas I can confirm that it's still happening in 4.1.1. I haven't tested 5.0.0 yet.

Comment From: rusackas

CC @eschutho who was looking into this sort of thing as of quite recently.

Volunteers/PRs/suggestions welcome!