danielalyoshin opened a new pull request, #45203: URL: https://github.com/apache/superset/pull/45203
<!--- Please write the PR title following the conventions at https://www.conventionalcommits.org/en/v1.0.0/ Example: fix(dashboard): load charts correctly --> ### SUMMARY <!--- Describe the change below, including rationale and design decisions --> A dataset timezone (`{"timezone": "..."}` in the dataset's extra settings, added in #37014) converts the time range bounds from that timezone to UTC in `get_time_filter`, and converts the returned values from UTC to that timezone in `normalize_dttm_col`. A `DATE` column holds calendar days with no time of day, so both conversions move it by part of a day, and what goes wrong depends on the database: * SQLite compares the text, so west of UTC the range selects one day late. * PostgreSQL, like other databases whose `convert_dttm` writes a `DATE` bound as a date only, cuts each bound to its date, so east of UTC the range selects one day early. * Everywhere, each value comes back as midnight UTC and is shown at a time of day in the dataset timezone: 2026-09-20 is shown as 2026-09-19 20:00 in New York and 2026-09-20 09:00 in Tokyo. With a day grain the chart labels the New York days one day early. This PR leaves `DATE` columns as they are, the same way #43173 made the Hours offset whole days for them: * `get_time_filter` keeps the bounds of a `DATE` column. The timezone still takes precedence over the Hours offset, and an unknown timezone still falls back to it. * `normalize_df` marks `DATE` columns with a new `DateColumn.date_only` flag, and `normalize_dttm_col` does not convert them. This covers the x-axis and the legacy `__timestamp` column. * The type check is the one `get_effective_hours_offset` already used, moved into `is_date_column_type` so both use it. Both parts are needed. With only the filter fixed, PostgreSQL in New York selects the right days but still labels them one day early. `DATETIME` and `TIMESTAMP` columns are not affected. One side effect, also noted in `UPDATING.md`: a column that Superset reads as a date type is left alone even if the database stores a time of day in it, such as Oracle `DATE`. Its filter bounds were already cut to the date by `OracleEngineSpec.convert_dttm`, and the Hours offset already treats it as whole days. Follow-up to #44805, which fixed time filters on SQLite `DATE` columns without a dataset timezone. Tests: * `test_time_filter_timezone.py`: filter bounds for a `DATE` column on SQLite and PostgreSQL in New York and Tokyo; the timezone still taking precedence over a whole-day Hours offset; an unknown timezone falling back to it; chart queries on an in-memory SQLite with and without a day grain and through the legacy `__timestamp` column, checking the rows and the shown values; a `TIMESTAMP` column that is still converted; `_is_date_only_column` for column objects and dicts. * `test_core.py`: `normalize_dttm_col` leaves a `date_only` column alone, and does not apply the offset in its place. On master 8 of the new tests in `test_time_filter_timezone.py` fail. The PostgreSQL New York filter case passes on master, because `TO_DATE` cuts its bounds back to the selected days. ### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF <!--- Skip this if not applicable --> Rows a chart query returns for a `DATE` column holding 2026-09-19 to 2026-09-22, with a time range from 2026-09-20 to 2026-09-22, through `/api/v1/chart/data` on PostgreSQL 16 and SQLite. The results are the same with and without a day grain. | Database | Dataset timezone | Before: days selected | Before: values returned | After | |----------|------------------|-----------------------|-------------------------|-------| | PostgreSQL | America/New_York | 09-20, 09-21 | 09-19 20:00, 09-20 20:00 | 09-20, 09-21 | | PostgreSQL | Asia/Tokyo | 09-19, 09-20 | 09-19 09:00, 09-20 09:00 | 09-20, 09-21 | | SQLite | America/New_York | 09-21, 09-22 | 09-20 20:00, 09-21 20:00 | 09-20, 09-21 | | SQLite | Asia/Tokyo | 09-20, 09-21 | 09-20 09:00, 09-21 09:00 | 09-20, 09-21 | Without a dataset timezone, and for `TIMESTAMP` columns, the results are the same before and after. ### TESTING INSTRUCTIONS <!--- Required! What steps can be taken to manually verify the changes? --> Unit tests: ```bash pytest tests/unit_tests/models/test_time_filter_timezone.py tests/unit_tests/models/test_hours_offset_bound_truncation.py tests/unit_tests/utils/test_core.py ``` By hand, on PostgreSQL: 1. `CREATE TABLE t (day DATE); INSERT INTO t VALUES ('2026-09-19'), ('2026-09-20'), ('2026-09-21'), ('2026-09-22');` 2. Create a dataset from `t`. In Edit dataset > Settings, set Extra to `{"timezone": "Asia/Tokyo"}`. 3. Make a table chart with `day` as a dimension, time grain Day, metric `COUNT(*)`, and a custom time range from 2026-09-20 to 2026-09-22. 4. In the chart's results, before this PR `day` is 2026-09-19 09:00 and 2026-09-20 09:00. With it, 2026-09-20 and 2026-09-21. 5. Set the timezone to `America/New_York`. Before this PR `day` is 2026-09-19 20:00 and 2026-09-20 20:00. With it, 2026-09-20 and 2026-09-21. ### ADDITIONAL INFORMATION <!--- Check any relevant boxes with "x" --> <!--- HINT: Include "Fixes #nnn" if you are fixing an existing issue --> - [ ] Has associated issue: - [ ] Required feature flags: - [ ] Changes UI - [ ] Includes DB Migration (follow approval process in [SIP-59](https://github.com/apache/superset/issues/13351)) - [ ] Migration is atomic, supports rollback & is backwards-compatible - [ ] Confirm DB migration upgrade and downgrade tested - [ ] Runtime estimates and downtime expectations provided - [ ] Introduces new feature or API - [ ] Removes existing feature or API -- 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]
