You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ru...@apache.org on 2026/05/21 05:27:06 UTC
(superset) branch master updated: fix(charts): handle PostgreSQL INTERVAL type in bar and pie charts (#34513)
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 7e74fc41920 fix(charts): handle PostgreSQL INTERVAL type in bar and pie charts (#34513)
7e74fc41920 is described below
commit 7e74fc4192099b39db6d15e221b678e1b54e29f1
Author: Evan Rusackas <ev...@preset.io>
AuthorDate: Wed May 20 22:26:59 2026 -0700
fix(charts): handle PostgreSQL INTERVAL type in bar and pie charts (#34513)
Co-authored-by: Claude <no...@anthropic.com>
---
superset/db_engine_specs/postgres.py | 42 +++++++++++++++++++++--
tests/unit_tests/db_engine_specs/test_postgres.py | 41 ++++++++++++++++++++--
2 files changed, 79 insertions(+), 4 deletions(-)
diff --git a/superset/db_engine_specs/postgres.py b/superset/db_engine_specs/postgres.py
index 4d62b5dd242..e1439153054 100644
--- a/superset/db_engine_specs/postgres.py
+++ b/superset/db_engine_specs/postgres.py
@@ -21,10 +21,11 @@ import logging
import re
from datetime import datetime
from re import Pattern
-from typing import Any, Optional, TYPE_CHECKING
+from typing import Any, Callable, Optional, TYPE_CHECKING
from flask_babel import gettext as __
-from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION, ENUM, JSON
+from sqlalchemy import types
+from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION, ENUM, INTERVAL, JSON
from sqlalchemy.dialects.postgresql.base import PGInspector
from sqlalchemy.engine.reflection import Inspector
from sqlalchemy.engine.url import URL
@@ -135,6 +136,34 @@ def parse_options(connect_args: dict[str, Any]) -> dict[str, str]:
return {token[0]: token[1] for token in tokens}
+def _normalize_interval(v: Any) -> Optional[float]:
+ """Convert PostgreSQL INTERVAL values to milliseconds.
+
+ psycopg2 and psycopg3 always return INTERVAL values as datetime.timedelta
+ objects. We convert to milliseconds so users can apply the built-in
+ "DURATION" number format for human-readable display (e.g.,
+ "1d 2h 30m 45s") and so the values participate cleanly in numeric
+ aggregations in bar/pie charts.
+
+ Returns None for the NULL case (preserves NULL semantics) and for any
+ unexpected non-timedelta type (avoids producing a mixed-type column
+ when an unfamiliar driver surfaces something other than timedelta).
+ """
+ if v is None:
+ return None
+ if hasattr(v, "total_seconds"):
+ return v.total_seconds() * 1000
+ # Defensive: psycopg2/3 should always hand us a timedelta. If a future
+ # driver doesn't, surface the surprise in the logs rather than silently
+ # dropping the value so operators can diagnose it.
+ logger.warning(
+ "Cannot normalize PostgreSQL INTERVAL value of type %s to numeric; "
+ "returning None.",
+ type(v).__name__,
+ )
+ return None
+
+
class PostgresBaseEngineSpec(BaseEngineSpec):
"""Abstract class for Postgres 'like' databases"""
@@ -526,8 +555,17 @@ class PostgresEngineSpec(BasicParametersMixin, PostgresBaseEngineSpec):
ENUM(),
GenericDataType.STRING,
),
+ (
+ re.compile(r"^interval", re.IGNORECASE),
+ INTERVAL(),
+ GenericDataType.NUMERIC,
+ ),
)
+ column_type_mutators: dict[types.TypeEngine, Callable[[Any], Any]] = {
+ INTERVAL: _normalize_interval,
+ }
+
@classmethod
def get_schema_from_engine_params(
cls,
diff --git a/tests/unit_tests/db_engine_specs/test_postgres.py b/tests/unit_tests/db_engine_specs/test_postgres.py
index c043bae3ef8..49dc70fe502 100644
--- a/tests/unit_tests/db_engine_specs/test_postgres.py
+++ b/tests/unit_tests/db_engine_specs/test_postgres.py
@@ -15,14 +15,14 @@
# specific language governing permissions and limitations
# under the License.
-from datetime import datetime
+from datetime import datetime, timedelta
from typing import Any, Optional
from unittest.mock import MagicMock
import pytest
from pytest_mock import MockerFixture
from sqlalchemy import column, types
-from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION, ENUM, JSON
+from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION, ENUM, INTERVAL, JSON
from sqlalchemy.engine.interfaces import Dialect
from sqlalchemy.engine.url import make_url
@@ -87,6 +87,8 @@ def test_convert_dttm(
("TIME", types.Time, None, GenericDataType.TEMPORAL, True),
# Boolean
("BOOLEAN", types.Boolean, None, GenericDataType.BOOLEAN, False),
+ # Interval (mapped to NUMERIC for chart rendering)
+ ("INTERVAL", INTERVAL, None, GenericDataType.NUMERIC, False),
],
)
def test_get_column_spec(
@@ -366,3 +368,38 @@ class TestRedshiftDetection:
spec.update_params_from_encrypted_extra(database, params)
assert "pool_events" not in params
+
+
+def test_interval_type_mutator() -> None:
+ """
+ DB Eng Specs (postgres): Test INTERVAL type mutator
+
+ INTERVAL values are converted to milliseconds so users can apply
+ the built-in "DURATION" number format for human-readable display.
+ """
+ mutator = spec.column_type_mutators[INTERVAL]
+
+ # Timedelta conversion — the only path psycopg2/psycopg3 actually
+ # exercises. Result is in milliseconds for compatibility with the
+ # DURATION formatter.
+ td = timedelta(days=1, hours=2, minutes=30, seconds=45)
+ assert mutator(td) == 95445000.0 # (1*86400 + 2*3600 + 30*60 + 45) * 1000
+
+ # Zero duration
+ assert mutator(timedelta(0)) == 0.0
+
+ # Negative interval
+ assert mutator(timedelta(days=-1)) == -86400000.0
+
+ # None preserves NULL semantics (not converted to 0)
+ assert mutator(None) is None
+
+ # Unexpected non-timedelta types fall through to the defensive
+ # `return None` (and emit a warning) rather than producing a
+ # mixed-type column.
+ assert mutator("1 day 02:30:45") is None
+ assert mutator("P1DT2H30M45S") is None
+ assert mutator(12345) is None
+ assert mutator(True) is None
+ assert mutator([1, 2, 3]) is None
+ assert mutator({"days": 1}) is None