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 50c1504cac4 fix(db_engine_specs): cast VARCHAR before Postgres
DATE_TRUNC (#43167)
50c1504cac4 is described below
commit 50c1504cac4184142848e966f733bc45907d1bd4
Author: nacretion <[email protected]>
AuthorDate: Wed Sep 9 07:38:37 2026 +0300
fix(db_engine_specs): cast VARCHAR before Postgres DATE_TRUNC (#43167)
---
superset/db_engine_specs/postgres.py | 17 +++++++++++-
tests/unit_tests/db_engine_specs/test_postgres.py | 34 +++++++++++++++++++++++
2 files changed, 50 insertions(+), 1 deletion(-)
diff --git a/superset/db_engine_specs/postgres.py
b/superset/db_engine_specs/postgres.py
index f7c44aa5262..cfcdbae60fa 100644
--- a/superset/db_engine_specs/postgres.py
+++ b/superset/db_engine_specs/postgres.py
@@ -306,17 +306,32 @@ class PostgresBaseEngineSpec(BaseEngineSpec):
time_grain: str | None,
) -> TimestampExpression:
"""
- Construct a timestamp expression while preserving pure ``DATE``
semantics.
+ Construct a timestamp expression for Postgres temporal columns.
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.
+ String columns explicitly marked as temporal are cast to ``TIMESTAMP``
before
+ applying a time grain because Postgres does not implicitly cast
strings for
+ ``DATE_TRUNC`` or ``EXTRACT``.
+
See https://github.com/apache/superset/issues/42254.
+ See https://github.com/apache/superset/issues/42386.
"""
expr = super().get_timestamp_expr(col, pdf, time_grain)
col_type = getattr(col, "type", None)
+ if (
+ time_grain
+ and isinstance(col_type, String)
+ and pdf not in ("epoch_s", "epoch_ms")
+ ):
+ expr = TimestampExpression(
+ expr.name.replace("{col}", "CAST({col} AS TIMESTAMP)"),
+ col,
+ type_=DateTime(),
+ )
# ``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):
diff --git a/tests/unit_tests/db_engine_specs/test_postgres.py
b/tests/unit_tests/db_engine_specs/test_postgres.py
index e70ee41798d..2903ef8761c 100644
--- a/tests/unit_tests/db_engine_specs/test_postgres.py
+++ b/tests/unit_tests/db_engine_specs/test_postgres.py
@@ -485,6 +485,40 @@ def test_get_timestamp_expr_datetime_column_not_cast() ->
None:
assert _compile(expr) == "DATE_TRUNC('day', event_ts)"
+def test_get_timestamp_expr_string_column_casts_to_timestamp() -> None:
+ """DB Eng Specs (postgres): temporal string columns are cast before
truncation."""
+ col = column("event_timestamp", type_=types.String())
+ expr = spec.get_timestamp_expr(col, None, "P1D")
+ assert _compile(expr) == "DATE_TRUNC('day', CAST(event_timestamp AS
TIMESTAMP))"
+
+
+def test_get_timestamp_expr_string_column_without_grain_not_cast() -> None:
+ """DB Eng Specs (postgres): strings without a time grain remain
unchanged."""
+ col = column("event_timestamp", type_=types.String())
+ expr = spec.get_timestamp_expr(col, None, None)
+ assert _compile(expr) == "event_timestamp"
+
+
+def test_get_timestamp_expr_epoch_string_column_not_cast() -> None:
+ """DB Eng Specs (postgres): timestamp casts are not added to epoch
expressions."""
+ col = column("event_timestamp", type_=types.String())
+ expr = spec.get_timestamp_expr(col, "epoch_s", "P1D")
+ assert _compile(expr) == (
+ "DATE_TRUNC('day', (timestamp 'epoch' + event_timestamp * interval '1
second'))"
+ )
+
+
+def test_get_timestamp_expr_string_column_casts_every_grain_reference() ->
None:
+ """DB Eng Specs (postgres): compound grains cast every string reference."""
+ col = column("event_timestamp", type_=types.String())
+ expr = spec.get_timestamp_expr(col, None, "PT5S")
+ assert _compile(expr) == (
+ "DATE_TRUNC('minute', CAST(event_timestamp AS TIMESTAMP)) "
+ "+ INTERVAL '5 seconds' * "
+ "FLOOR(EXTRACT(SECOND FROM CAST(event_timestamp AS TIMESTAMP)) / 5)"
+ )
+
+
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
