This is an automated email from the ASF dual-hosted git repository.
rusackas pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/superset.git
The following commit(s) were added to refs/heads/master by this push:
new c792752a58b fix(db_engine_specs): preserve DATE semantics when
applying Postgres time grains (#42254) (#42329)
c792752a58b is described below
commit c792752a58b1e4d553df95beb70a8a20f8f4bed0
Author: Amogh Atreya <[email protected]>
AuthorDate: Tue Jul 28 06:01:28 2026 +0530
fix(db_engine_specs): preserve DATE semantics when applying Postgres time
grains (#42254) (#42329)
Co-authored-by: Amin Ghadersohi <[email protected]>
---
superset/db_engine_specs/postgres.py | 27 +++++++++++++
tests/unit_tests/db_engine_specs/test_postgres.py | 47 +++++++++++++++++++++++
2 files changed, 74 insertions(+)
diff --git a/superset/db_engine_specs/postgres.py
b/superset/db_engine_specs/postgres.py
index 4be3006a1ad..1945fa3f303 100644
--- a/superset/db_engine_specs/postgres.py
+++ b/superset/db_engine_specs/postgres.py
@@ -29,6 +29,7 @@ from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION,
ENUM, INTERVAL, JSO
from sqlalchemy.dialects.postgresql.base import PGInspector
from sqlalchemy.engine.reflection import Inspector
from sqlalchemy.engine.url import URL
+from sqlalchemy.sql.expression import ColumnClause
from sqlalchemy.types import Date, DateTime, String
from superset.constants import TimeGrain
@@ -36,6 +37,7 @@ from superset.db_engine_specs.base import (
BaseEngineSpec,
BasicParametersMixin,
DatabaseCategory,
+ TimestampExpression,
)
from superset.errors import ErrorLevel, SupersetError, SupersetErrorType
from superset.exceptions import SupersetException, SupersetSecurityException
@@ -256,6 +258,31 @@ class PostgresBaseEngineSpec(BaseEngineSpec):
def epoch_to_dttm(cls) -> str:
return "(timestamp 'epoch' + {col} * interval '1 second')"
+ @classmethod
+ def get_timestamp_expr(
+ cls,
+ col: ColumnClause,
+ pdf: str | None,
+ time_grain: str | None,
+ ) -> TimestampExpression:
+ """
+ Construct a timestamp expression while preserving pure ``DATE``
semantics.
+
+ Applying ``DATE_TRUNC`` to a ``DATE`` column implicitly casts the
value to
+ ``TIMESTAMP``, which can trigger unwanted timezone conversion on the
client
+ and shift the displayed date by a day. To avoid this, the truncated
value is
+ cast back to ``DATE`` when the source column is a pure ``DATE`` type.
+
+ See https://github.com/apache/superset/issues/42254.
+ """
+ expr = super().get_timestamp_expr(col, pdf, time_grain)
+ col_type = getattr(col, "type", None)
+ # ``DateTime``/``TIMESTAMP`` are distinct SQLAlchemy types (not
subclasses
+ # of ``Date``), so this only matches pure ``DATE`` columns.
+ if time_grain and isinstance(col_type, Date):
+ return TimestampExpression(f"CAST({expr.name} AS DATE)", col,
type_=Date())
+ return expr
+
@classmethod
def convert_dttm(
cls, target_type: str, dttm: datetime, db_extra: dict[str, Any] | None
= None
diff --git a/tests/unit_tests/db_engine_specs/test_postgres.py
b/tests/unit_tests/db_engine_specs/test_postgres.py
index 49dc70fe502..287fcec38a3 100644
--- a/tests/unit_tests/db_engine_specs/test_postgres.py
+++ b/tests/unit_tests/db_engine_specs/test_postgres.py
@@ -22,6 +22,7 @@ from unittest.mock import MagicMock
import pytest
from pytest_mock import MockerFixture
from sqlalchemy import column, types
+from sqlalchemy.dialects import postgresql
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION, ENUM, INTERVAL,
JSON
from sqlalchemy.engine.interfaces import Dialect
from sqlalchemy.engine.url import make_url
@@ -370,6 +371,52 @@ class TestRedshiftDetection:
assert "pool_events" not in params
+def _compile(expr: Any) -> str:
+ return str(expr.compile(None, dialect=postgresql.dialect()))
+
+
+def test_get_timestamp_expr_date_column_casts_back_to_date() -> None:
+ """
+ DB Eng Specs (postgres): a time grain on a pure DATE column casts the
+ ``DATE_TRUNC`` result back to DATE to avoid timezone-driven date shifts.
+
+ See https://github.com/apache/superset/issues/42254.
+ """
+ col = column("event_date", type_=types.Date())
+ expr = spec.get_timestamp_expr(col, None, "P1D")
+ assert _compile(expr) == "CAST(DATE_TRUNC('day', event_date) AS DATE)"
+
+
+def test_get_timestamp_expr_datetime_column_not_cast() -> None:
+ """
+ DB Eng Specs (postgres): DATETIME/TIMESTAMP columns keep their timestamp
+ semantics and are not cast back to DATE.
+ """
+ col = column("event_ts", type_=types.DateTime())
+ expr = spec.get_timestamp_expr(col, None, "P1D")
+ assert _compile(expr) == "DATE_TRUNC('day', event_ts)"
+
+
+def test_get_timestamp_expr_date_column_without_grain_not_cast() -> None:
+ """
+ DB Eng Specs (postgres): without a time grain there is no DATE_TRUNC, so
the
+ column is left untouched.
+ """
+ col = column("event_date", type_=types.Date())
+ expr = spec.get_timestamp_expr(col, None, None)
+ assert _compile(expr) == "event_date"
+
+
+def test_get_timestamp_expr_untyped_column_not_cast() -> None:
+ """
+ DB Eng Specs (postgres): columns without a known type (e.g. raw
expressions)
+ are not cast to DATE.
+ """
+ col = column("some_expr")
+ expr = spec.get_timestamp_expr(col, None, "P1Y")
+ assert _compile(expr) == "DATE_TRUNC('year', some_expr)"
+
+
def test_interval_type_mutator() -> None:
"""
DB Eng Specs (postgres): Test INTERVAL type mutator
