fix(sqlite): write midnight as a bare date for DATE columns in time filters by danielalyoshin · Pull Request #44805 · apache/superset

danielalyoshin · 2026-09-29T17:18:54Z

SUMMARY

SqliteEngineSpec.convert_dttm only handles String and DateTime columns and returns None for Date. Superset then falls back to a literal such as '2026-09-20 00:00:00.000000'. SQLite has no date type, so a DATE column usually holds text such as 2026-09-20, and that literal sorts after the day it starts. A time range on a DATE column therefore starts and ends one day late: 2026-09-20 to 2026-09-22 returns 09-21 and 09-22 instead of 09-20 and 09-21.

This PR writes midnight as a bare date for DATE columns, the same fix #44505 made for D1, and close to what #43355 did for Google Sheets. A time other than midnight keeps its time part ('2026-09-20 12:00:00'), which sorts between two days, so each day counts as its midnight, as in a comparison of dates. DATETIME, TIMESTAMP and text columns are unchanged.

Specs that inherit convert_dttm from SqliteEngineSpec:

ShillelaghEngineSpec and SupersetEngineSpec (the meta database) always write a bare date for a DATE column, as GSheetsEngineSpec did. Shillelagh reads a date filter with date.fromisoformat, which rejects a time part and drops the filter without an error, so on the meta database a filter on a DATE column was dropped and every row came back. A bound with a time part is cut to its date there.
GSheetsEngineSpec gets the same behavior from ShillelaghEngineSpec, and CloudflareD1EngineSpec did the same as the new SQLite code, so both overrides are removed. Neither changes behavior.

One behavior change to be aware of, also noted in UPDATING.md: for a DATE column the literal now comes from the engine spec, so the column's Datetime format (python_date_format) is no longer used in its time filters. SQLite already works this way for TEXT and DATETIME columns, and #44505 and #43355 did the same for D1 and Google Sheets. A DATE column that holds another format, such as 20260920 (%Y%m%d), 09/20/2026 (%m/%d/%Y) or epoch seconds (epoch_s), got the right rows before and gets none now. Columns declared as INTEGER are not affected. Letting a set Datetime format win over the engine spec would fix this for every database, so I plan to do that in a follow-up PR.

Not in this PR: with a dataset timezone (extra.timezone), get_time_filter moves the bounds off midnight, so a range on a DATE column is still one day off. That happens on master too, and on Postgres as well, so it will get its own PR.

Tests:

test_sqlite.py: convert_dttm for DATE at midnight and at other times, and a range test on an in-memory SQLite. It builds the filter with get_time_filter, as a chart query does, with and without a day grain, for SQLite and D1. It replaces the copy in test_d1.py.
test_shillelagh.py: convert_dttm for DATE on ShillelaghEngineSpec and SupersetEngineSpec.
extensions/test_sqlalchemy.py: a range test on a DATE column through the meta database, at midnight and at noon.

On master the midnight cases of the SQLite range test return 09-21 and 09-22, and both cases of the meta database test return all four days.

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

Rows returned with the time filter a chart query builds, for a table holding 2026-09-19 to 2026-09-22:

Column and range	Before	After
`DATE`, 09-20 to 09-22	09-21, 09-22	09-20, 09-21
`DATE`, 09-20 12:00 to 09-22 12:00	09-21, 09-22	09-21, 09-22
`DATETIME`, 09-20 to 09-22	09-20, 09-21	09-20, 09-21
`DATE` on the meta database, 09-20 to 09-22	all four days	09-20, 09-21
`DATE` on the meta database, 09-20 12:00 to 09-22 12:00	all four days	09-20, 09-21
`DATE` holding `20260920`, format `%Y%m%d`, 09-20 to 09-22	09-20, 09-21	none
`DATE` holding `09/20/2026`, format `%m/%d/%Y`, 09-20 to 09-22	09-20, 09-21	none
`DATE` holding epoch seconds, format `epoch_s`, 09-20 to 09-22	09-20, 09-21	none

TESTING INSTRUCTIONS

Unit tests:

pytest tests/unit_tests/db_engine_specs/test_sqlite.py tests/unit_tests/db_engine_specs/test_shillelagh.py tests/unit_tests/db_engine_specs/test_d1.py tests/unit_tests/extensions/test_sqlalchemy.py

By hand:

Make a SQLite file with CREATE TABLE t (day DATE); INSERT INTO t VALUES ('2026-09-19'), ('2026-09-20'), ('2026-09-21'), ('2026-09-22'); and connect it to Superset (SQLite needs PREVENT_UNSAFE_DB_CONNECTIONS = False).
Create a dataset from t and a table chart on day with a custom time range from 2026-09-20 to 2026-09-22.
Before this PR the chart shows 09-21 and 09-22. With it, 09-20 and 09-21.

ADDITIONAL INFORMATION

Has associated issue:
Required feature flags:
Changes UI
Includes DB Migration (follow approval process in SIP-59)
- 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

…ilters SqliteEngineSpec.convert_dttm returned None for DATE columns, so Superset fell back to a literal such as '2026-09-20 00:00:00.000000'. SQLite compares a DATE stored as 'YYYY-MM-DD' text as text, so a range from 09-20 to 09-22 returned 09-21 and 09-22. Midnight is now written as a bare date and other times keep their time part, the same as the D1 spec. Shillelagh and the Superset meta database inherit the change. The meta database used to drop such a DATE filter, because shillelagh cannot read a date with a time part, so whole-day ranges now apply there too.

bito-code-review · 2026-09-29T17:24:21Z

The flagged issue is correct. The current implementation in superset/db_engine_specs/sqlite.py correctly handles midnight for DATE columns by returning a bare date string, but it does not explicitly handle non-midnight times for DATE columns, which may lead to unexpected behavior depending on how the database interprets the literal.

To resolve this, you should ensure that non-midnight times for DATE columns are formatted consistently with the database's expectations. The current implementation already includes a fallback that handles types.Date in the second if block, which should produce the expected ISO format for non-midnight times.

Regarding other comments on this PR, I have checked the available review comments and there are no additional comments to address at this time.

superset/db_engine_specs/sqlite.py

if isinstance(sqla_type, types.Date) and dttm.time() == time.min:
            return f"'{dttm.date().isoformat()}'"
        if isinstance(sqla_type, (types.String, types.Date, types.DateTime)):
            return f"'{dttm.isoformat(sep=" ", timespec="seconds")}'"

bito-code-review

Code Review Agent Run #63f777

Actionable Suggestions - 1

superset/db_engine_specs/sqlite.py - 1
- Midnight branch catches DateTime · Line 177-177

Review Details

Files reviewed - 4 · Commit Range: a7d5a5c..a7d5a5c
- superset/db_engine_specs/sqlite.py
- tests/unit_tests/db_engine_specs/test_shillelagh.py
- tests/unit_tests/db_engine_specs/test_sqlite.py
- tests/unit_tests/extensions/test_sqlalchemy.py
Files skipped - 0
Tools
- MyPy (Static Code Analysis) - ✔︎ Successful
- Astral Ruff (Static Code Analysis) - ✔︎ Successful
- Whispers (Secret Scanner) - ✔︎ Successful
- Detect-secrets (Secret Scanner) - ✔︎ Successful

Bito Usage Guide

Commands

Type the following command in the pull request comment and save the comment.

/review - Manually triggers an incremental AI Review.
/review full - Manually triggers a full AI Review.
/pause - Pauses automatic reviews on this pull request.
/resume - Resumes automatic reviews.
/resolve - Marks all Bito-posted review comments as resolved.
/abort - Cancels all in-progress reviews.

Refer to the documentation for additional commands.

Configuration

This repository uses Superset You can customize the agent settings here or contact your Bito workspace admin at evan@preset.io.

Documentation & Help

AI Code Review powered by

codecov · 2026-09-29T17:38:28Z

Codecov Report

✅ All modified and coverable lines are covered by tests.
✅ Project coverage is 81.59%. Comparing base (3f8a236) to head (3af6f08).
⚠️ Report is 15 commits behind head on master.

Additional details and impacted files

@@           Coverage Diff           @@
##           master   #44805   +/-   ##
=======================================
  Coverage   81.58%   81.59%           
=======================================
  Files        2977     2977           
  Lines      180845   180837    -8     
  Branches    41853    41852    -1     
=======================================
- Hits       147550   147548    -2     
+ Misses      30572    30568    -4     
+ Partials     2723     2721    -2

Flag	Coverage Δ
hive	`36.67% <53.84%> (-0.01%)`	⬇️
mysql	`55.92% <53.84%> (-0.01%)`	⬇️
postgres	`55.92% <53.84%> (-0.01%)`	⬇️
presto	`38.59% <53.84%> (-0.01%)`	⬇️
python	`85.73% <100.00%> (+<0.01%)`	⬆️
sqlite	`55.65% <53.84%> (+<0.01%)`	⬆️
unit	`78.52% <100.00%> (+<0.01%)`	⬆️

Flags with carried forward coverage won't be shown. Click here to find out more.

☔ View full report in Codecov by Harness.
📢 Have feedback on the report? Share it here.

🚀 New features to boost your workflow:

❄️ Test Analytics: Detect flaky tests, report on failures, and find test suite problems.
📦 JS Bundle Analysis: Save yourself from yourself by tracking and limiting bundle sizes in JS merges.

Shillelagh drops a date filter with a time part without an error, so on the meta database a DATE bound other than midnight returned every row. ShillelaghEngineSpec now always writes a bare date for DATE columns, as the GSheets spec did, so the GSheets override is removed. The D1 override did the same as the SQLite one and is removed too. The SQLite range test builds its filter with get_time_filter, with and without a day grain, for SQLite and D1, and replaces the copy in test_d1.py. The meta database test covers a bound with a time part. UPDATING.md notes that DATE columns on SQLite no longer use the Datetime format.

# Conflicts: # UPDATING.md

bito-code-review · 2026-09-30T17:47:04Z

Code Review Agent Run #db372e

Actionable Suggestions - 0

Additional Suggestions - 1

superset/db_engine_specs/shillelagh.py - 1
- DATE filter boundary truncation · Line 74-75
  
  Truncating any non-midnight time to its date (line 75) changes boundary semantics for time-of-day filters on DATE columns: `day < '2026-09-22 12:00'` becomes `day < '2026-09-22'`, excluding 09-22. This is a deliberate, documented improvement over the prior silently-dropped filter, but worth confirming the upper-bound off-by-one is acceptable.

Review Details

Files reviewed - 8 · Commit Range: a7d5a5c..3af6f08
- superset/db_engine_specs/d1.py
- superset/db_engine_specs/gsheets.py
- superset/db_engine_specs/shillelagh.py
- superset/db_engine_specs/sqlite.py
- tests/unit_tests/db_engine_specs/test_d1.py
- tests/unit_tests/db_engine_specs/test_shillelagh.py
- tests/unit_tests/db_engine_specs/test_sqlite.py
- tests/unit_tests/extensions/test_sqlalchemy.py
Files skipped - 1
- UPDATING.md - Reason: Filter setting
Tools
- MyPy (Static Code Analysis) - ✔︎ Successful
- Astral Ruff (Static Code Analysis) - ✔︎ Successful
- Whispers (Secret Scanner) - ✔︎ Successful
- Detect-secrets (Secret Scanner) - ✔︎ Successful

Bito Usage Guide

Commands

Type the following command in the pull request comment and save the comment.

/review - Manually triggers an incremental AI Review.
/review full - Manually triggers a full AI Review.
/pause - Pauses automatic reviews on this pull request.
/resume - Resumes automatic reviews.
/resolve - Marks all Bito-posted review comments as resolved.
/abort - Cancels all in-progress reviews.

Refer to the documentation for additional commands.

Configuration

This repository uses Superset You can customize the agent settings here or contact your Bito workspace admin at evan@preset.io.

Documentation & Help

AI Code Review powered by

aminghadersohi

The gsheets.py conflict with #44695 is the datetime import line only: keeping master's line, the merged tree passes the engine-spec tests with unchanged convert_dttm output. I checked the python_date_format trade-off and agree with it; only the UPDATING note should say what breaks.

aminghadersohi · 2026-10-01T18:14:07Z

+one day late on `DATE` columns holding `YYYY-MM-DD` text. A `DATE` column that
+holds values in another format, such as `20260920`, `09/20/2026` or epoch
+seconds, is no longer filtered correctly. Columns declared as `INTEGER` are not
+affected.


Measured on SQLite: with Datetime format %Y%m%d, %m/%d/%Y or epoch_s, a two-day range on a DATE column returns 2 rows on master and 0 here, which is what an operator can spot. The Shillelagh cut of non-midnight bounds isn't noted either.

Suggested change

one day late on `DATE` columns holding `YYYY-MM-DD` text. A `DATE` column that

holds values in another format, such as `20260920`, `09/20/2026` or epoch

seconds, is no longer filtered correctly. Columns declared as `INTEGER` are not

affected.

one day late on `DATE` columns holding `YYYY-MM-DD` text. A `DATE` column that

holds values in another format, such as `20260920`, `09/20/2026` or epoch

seconds, now matches no rows, even with a Datetime format set. Columns declared

as `INTEGER` are not affected. On Shillelagh and the meta database, a bound

with a time of day is cut to its date.

pull-request-size Bot added the size/L label Sep 29, 2026

codeant-ai-for-open-source Bot reviewed Sep 29, 2026

View reviewed changes

Comment thread superset/db_engine_specs/sqlite.py

bito-code-review Bot reviewed Sep 29, 2026

View reviewed changes

Comment thread superset/db_engine_specs/sqlite.py

danielalyoshin added 2 commits September 30, 2026 10:29

Merge branch 'master' into fix/sqlite-date-filter

3af6f08

# Conflicts: # UPDATING.md

github-actions Bot added the requires:rebase Requires rebasing on top of current master label Oct 1, 2026

sadpandajoe requested review from alexandrusoare and aminghadersohi October 1, 2026 17:11

aminghadersohi reviewed Oct 1, 2026

View reviewed changes

Navigation Menu

fix(sqlite): write midnight as a bare date for DATE columns in time filters - #44805

fix(sqlite): write midnight as a bare date for DATE columns in time filters#44805
danielalyoshin wants to merge 3 commits into
apache:masterfrom
sqlalchemy-cf-d1:fix/sqlite-date-filter

danielalyoshin commented Sep 29, 2026 •

edited

Loading

Uh oh!

Uh oh!

bito-code-review Bot commented Sep 29, 2026

Uh oh!

bito-code-review Bot left a comment •

edited

Loading

Uh oh!

Uh oh!

codecov Bot commented Sep 29, 2026 •

edited

Loading

Uh oh!

bito-code-review Bot commented Sep 30, 2026 •

edited

Loading

Code Review Agent Run #db372e

Uh oh!

aminghadersohi left a comment

Uh oh!

aminghadersohi Oct 1, 2026

Uh oh!

This branch has not been deployed

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Conversation

danielalyoshin commented Sep 29, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

SUMMARY

BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF

TESTING INSTRUCTIONS

ADDITIONAL INFORMATION

Uh oh!

Uh oh!

bito-code-review Bot commented Sep 29, 2026

Uh oh!

bito-code-review Bot left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Code Review Agent Run #63f777

Uh oh!

Uh oh!

codecov Bot commented Sep 29, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Codecov Report

Uh oh!

bito-code-review Bot commented Sep 30, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Code Review Agent Run #db372e

Uh oh!

aminghadersohi left a comment

Choose a reason for hiding this comment

Uh oh!

aminghadersohi Oct 1, 2026

Choose a reason for hiding this comment

Uh oh!

This branch has not been deployed

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

danielalyoshin commented Sep 29, 2026 •

edited

Loading

bito-code-review Bot left a comment •

edited

Loading

codecov Bot commented Sep 29, 2026 •

edited

Loading

bito-code-review Bot commented Sep 30, 2026 •

edited

Loading