dhruv-techdev opened a new issue, #45857:
URL: https://github.com/apache/superset/issues/45857
### Bug description
The `Week` time grain for Oracle and Impala truncates with the `'WW'`
format. In both databases, `'WW'` means "same day of the week as January 1st",
not a fixed week start. Because of this:
- The weekday a week starts on changes every year (2025 weeks start on
Wednesday, 2026 weeks on Thursday).
- Every year ends with a stub "week" of 1 day (2 days in a leap year), e.g.
`2025-12-31` gets its own bucket.
- Weeks never cross a year boundary, so week-over-week comparisons near New
Year are wrong.
Every other engine spec truncates `Week` to a fixed start day.
Code (current `master`):
- `superset/db_engine_specs/oracle.py`: `TimeGrain.WEEK: "TRUNC(CAST({col}
as DATE), 'WW')"`
- `superset/db_engine_specs/impala.py`: `TimeGrain.WEEK: "TRUNC({col},
'WW')"`
### Steps to reproduce
1. Connect an Oracle (or Impala) database and create a dataset with a
date/timestamp column covering late Dec 2025 – early Jan 2026.
2. Create a time-series chart with **Time grain = Week**.
3. Check the generated SQL ("View query") and the x-axis buckets.
4. Or run directly on Oracle:
```sql
SELECT d, TRUNC(d, 'WW') AS ww, TRUNC(d, 'IW') AS iw
FROM (
SELECT DATE '2025-01-06' d FROM dual UNION ALL
SELECT DATE '2025-12-30' FROM dual UNION ALL
SELECT DATE '2025-12-31' FROM dual UNION ALL
SELECT DATE '2026-01-05' FROM dual
);
### Expected vs actual (simulated from the documented `WW` semantics)
| Date | `'WW'` (actual) | Expected (Monday week start) |
|---|---|---|
| 2025-01-06 (Mon) | 2025-01-01 (Wed) | 2025-01-06 |
| 2025-12-30 (Tue) | 2025-12-24 (Wed) | 2025-12-29 |
| 2025-12-31 (Wed) | 2025-12-31 (1-day week) | 2025-12-29 |
| 2026-01-05 (Mon) | 2026-01-01 (Thu) | 2026-01-05 |
| 2024-12-31 (Tue) | 2024-12-30 (2-day week) | 2024-12-30 |
**Expected:** weeks start on a fixed day, as they do for other engines.
### Suggested fix
- **Oracle:** use `'IW'` (ISO week, always Monday, independent of
`NLS_TERRITORY`). Avoid `'D'`/`'DY'`, whose start day depends on the session's
NLS settings.
- **Impala:** `TRUNC` doesn't appear to support `'IW'`; candidates are
`TRUNC({col}, 'D')` or `DATE_TRUNC('WEEK', {col})` (Monday start). Confirm
against a real Impala instance.
- This changes existing week buckets in saved charts, so it probably needs
an `UPDATING.md` entry.
### Screenshots/recordings
<img width="948" height="726" alt="Image"
src="https://github.com/user-attachments/assets/7aa49e71-c4df-4be3-a741-c3f29b7da7b1";
/>
### Superset version
master / latest-dev
### Python version
3.11
### Node version
18 or greater
### Browser
Chrome
### Additional context
Affects any chart using the Week time grain on Oracle or Impala datasets. No
feature flags involved; this is backend SQL generation in the DB engine specs.
The `'WW'` expressions date back to at least the 2019 split of
`db_engine_specs` (#7676).
Related but distinct:
- #8385: Hive rejected `TRUNC(..., 'WW')` entirely (not about week start).
- #37608 / #37702: changed Oracle grains for `DATE` vs `TIMESTAMP` handling;
`'WW'` was kept as is.
References:
- Oracle ROUND/TRUNC date format models:
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROUND-and-TRUNC-Date-Functions.html
- Impala TRUNC():
https://impala.apache.org/docs/build/html/topics/impala_datetime_functions.html
### Checklist
- [x] I have searched Superset docs and Slack and didn't find a solution to
my problem.
- [x] I have searched the GitHub issue tracker and didn't find a similar bug
report.
- [x] 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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
