Repository navigation
Time Grain "Week starting Monday" doesn't select data correctly #31927
Description
Bug description
Hi,
While migrating a weekly dashboard from Excel to Superset, I found that time grain "Week starting Monday" doesn't select the proper data set.
As far as I understand, when selecting "Week starting Monday" should consider a week from Monday to Sunday, and using Monday as the label for that data aggregation. While time grain "Week ending Sunday" should consider the same time frame but will label the data aggregation uising the Sunday date. Despite the label, both should take the same days for each week.
When I select "Week ending Sunday" I obtain the correct sum for the week, however, when using "Week starting Monday" it takes data from Tuesday to Monday rather than from Monday to Sunday.
I'm attaching the dataset I'm using and two screenshots for the two charts.
I'm using superset 4.1.1
Warm regards.
Screenshots/recordings
Superset version
master / latest-dev
Python version
3.11
Node version
16
Browser
Chrome
Additional context
No response
Checklist
- I have searched Superset docs and Slack and didn't find a solution to my problem.
- I have searched the GitHub issue tracker and didn't find a similar bug report.
- I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.
Activity
dosubot commentedon Jan 19, 2025
Hey @AdrianMastronardi! I'm here to help you with any bugs, questions, or contribution guidance you may need. Let's squash those bugs together! 🐞
The issue you're experiencing with the "Week starting Monday" time grain in Superset 4.1.1 is due to the way the time grain expression is defined in the code. The expression for
TimeGrain.WEEK_STARTING_MONDAYcalculates the start of the week by finding the previous Monday and then subtracting 7 days, which effectively shifts the start of the week to Tuesday of the previous week. This results in a week that runs from Tuesday to Monday, rather than the expected Monday to Sunday [1].This behavior is inconsistent with the expected behavior, which should format the date range starting from Monday to the following Sunday [2]. There is a known issue related to this in the Superset GitHub repository, and it has been mentioned as a bug fix in the changelog [3].
To address this, you might need to adjust the time grain expression in your Superset configuration or wait for an update that resolves this issue.
To continue talking to Dosu, mention @dosu.
Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other
mistercrunch commentedon Jan 28, 2025
Which database backend are you using? The SQL function is defined on a per-engine basis, for example the logic for bigquery lives here https://github.com/apache/superset/blob/master/superset/db_engine_specs/bigquery.py#L182
By knowing the backend we can fine where the logic is applied based in this folder https://github.com/apache/superset/blob/master/superset/db_engine_specs/
Also looking at "View Query" in Explore would help understanding whether/why the SQL generated is wrong
Also note that on some databases, time zones handling can create confusion, where there may be time-zone-related semantics at the column-level and/or at the client-settings level.
AdrianMastronardi commentedon Jan 28, 2025
I'm using SQLite as backend. I confirm that there is no issue with timezone.
I think that you nailed it:
superset/superset/db_engine_specs/sqlite.py
Line 95 in 1b375b7
I will prepare the PR.
mistercrunch commentedon Jan 28, 2025
Glad we pinpointed it! Might be worth doing a full scan of all the time-related functions in that neighborhood. sqlite has its quirks, and if the author got that one wrong, it's possible other functions have similar issues.
AdrianMastronardi commentedon Jan 28, 2025
I reviewed all the WEEK_STARTING_* and WEEK_ENDING_* and with my fix all works fine.
I will do a full review of the remaining during the week.
fix(issue #31927): TimeGrain.WEEK_STARTING_MONDAY (#32015)
fix(issue apache#31927): TimeGrain.WEEK_STARTING_MONDAY (apache#32015)
fix(issue apache#31927): TimeGrain.WEEK_STARTING_MONDAY (apache#32015)
5.0 test (#36)