This is an automated email from the ASF dual-hosted git repository.
aminghadersohi 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 3e36b242a61 fix(datafusion): render temporal SQL that DataFusion
accepts (#44700)
3e36b242a61 is described below
commit 3e36b242a61337a40877a8f545e077f1b1879287
Author: Amin Ghadersohi <[email protected]>
AuthorDate: Thu Oct 1 00:01:53 2026 +1000
fix(datafusion): render temporal SQL that DataFusion accepts (#44700)
---
superset/db_engine_specs/datafusion.py | 34 ++++++++++++++++------
.../unit_tests/db_engine_specs/test_datafusion.py | 32 +++++++-------------
2 files changed, 36 insertions(+), 30 deletions(-)
diff --git a/superset/db_engine_specs/datafusion.py
b/superset/db_engine_specs/datafusion.py
index ec7a9ac4f64..5e344b97965 100644
--- a/superset/db_engine_specs/datafusion.py
+++ b/superset/db_engine_specs/datafusion.py
@@ -25,6 +25,20 @@ from superset.db_engine_specs import BaseEngineSpec
from superset.db_engine_specs.base import DatabaseCategory
+def _date_bin(interval: str) -> str:
+ """
+ Bucket from the Unix epoch without unsupported interval/Float64 arithmetic.
+
+ The UTC-epoch origin aligns buckets to UTC, while ``DATE_TRUNC`` aligns to
+ the column's local time. On timezone-aware columns in zones with a :45
+ offset (e.g. Asia/Kathmandu), sub-hour grains therefore land on different
+ boundaries than the hour grain: PT30M maps 13:32:33 to 13:15, PT1H to
13:00.
+ A per-zone origin is not possible because DataFusion only accepts a literal
+ origin.
+ """
+ return f"DATE_BIN(INTERVAL '{interval}', {{col}}, TIMESTAMP '1970-01-01
00:00:00')"
+
+
class DataFusionEngineSpec(BaseEngineSpec):
engine_name = "Apache DataFusion"
engine = "datafusion"
@@ -59,13 +73,13 @@ class DataFusionEngineSpec(BaseEngineSpec):
_time_grain_expressions = {
None: "{col}",
TimeGrain.SECOND: "DATE_TRUNC('second', {col})",
- TimeGrain.FIVE_SECONDS: "DATE_TRUNC('minute', {col}) + INTERVAL '5
seconds' * FLOOR(EXTRACT(SECOND FROM {col}) / 5)", # noqa: E501
- TimeGrain.THIRTY_SECONDS: "DATE_TRUNC('minute', {col}) + INTERVAL '30
seconds' * FLOOR(EXTRACT(SECOND FROM {col}) / 30)", # noqa: E501
+ TimeGrain.FIVE_SECONDS: _date_bin("5 seconds"),
+ TimeGrain.THIRTY_SECONDS: _date_bin("30 seconds"),
TimeGrain.MINUTE: "DATE_TRUNC('minute', {col})",
- TimeGrain.FIVE_MINUTES: "DATE_TRUNC('hour', {col}) + INTERVAL '5
minutes' * FLOOR(EXTRACT(MINUTE FROM {col}) / 5)", # noqa: E501
- TimeGrain.TEN_MINUTES: "DATE_TRUNC('hour', {col}) + INTERVAL '10
minutes' * FLOOR(EXTRACT(MINUTE FROM {col}) / 10)", # noqa: E501
- TimeGrain.FIFTEEN_MINUTES: "DATE_TRUNC('hour', {col}) + INTERVAL '15
minutes' * FLOOR(EXTRACT(MINUTE FROM {col}) / 15)", # noqa: E501
- TimeGrain.THIRTY_MINUTES: "DATE_TRUNC('hour', {col}) + INTERVAL '30
minutes' * FLOOR(EXTRACT(MINUTE FROM {col}) / 30)", # noqa: E501
+ TimeGrain.FIVE_MINUTES: _date_bin("5 minutes"),
+ TimeGrain.TEN_MINUTES: _date_bin("10 minutes"),
+ TimeGrain.FIFTEEN_MINUTES: _date_bin("15 minutes"),
+ TimeGrain.THIRTY_MINUTES: _date_bin("30 minutes"),
TimeGrain.HOUR: "DATE_TRUNC('hour', {col})",
TimeGrain.DAY: "DATE_TRUNC('day', {col})",
TimeGrain.WEEK: "DATE_TRUNC('week', {col})",
@@ -84,9 +98,11 @@ class DataFusionEngineSpec(BaseEngineSpec):
) -> str | None:
sqla_type = cls.get_sqla_column_type(target_type)
+ # DataFusion's to_timestamp()/to_date() take chrono format strings, not
+ # Oracle/PostgreSQL ones, so render ANSI typed literals instead.
if isinstance(sqla_type, types.Date):
- return f"TO_DATE('{dttm.date().isoformat()}', 'YYYY-MM-DD')"
+ return f"DATE '{dttm.date().isoformat()}'"
if isinstance(sqla_type, types.DateTime):
- dttm_formatted = dttm.isoformat(sep=" ", timespec="milliseconds")
- return f"""TO_TIMESTAMP('{dttm_formatted}', 'YYYY-MM-DD
HH24:MI:SS.FFF')"""
+ dttm_formatted = dttm.isoformat(sep=" ", timespec="microseconds")
+ return f"TIMESTAMP '{dttm_formatted}'"
return None
diff --git a/tests/unit_tests/db_engine_specs/test_datafusion.py
b/tests/unit_tests/db_engine_specs/test_datafusion.py
index 0595f885cfd..a369cf79ebc 100644
--- a/tests/unit_tests/db_engine_specs/test_datafusion.py
+++ b/tests/unit_tests/db_engine_specs/test_datafusion.py
@@ -26,15 +26,11 @@ from tests.unit_tests.fixtures.common import dttm # noqa:
F401
@pytest.mark.parametrize(
"target_type,expected_result",
[
- ("Date", "TO_DATE('2019-01-02', 'YYYY-MM-DD')"),
- (
- "DateTime",
- "TO_TIMESTAMP('2019-01-02 03:04:05.678', 'YYYY-MM-DD
HH24:MI:SS.FFF')",
- ),
- (
- "TimeStamp",
- "TO_TIMESTAMP('2019-01-02 03:04:05.678', 'YYYY-MM-DD
HH24:MI:SS.FFF')",
- ),
+ ("Date", "DATE '2019-01-02'"),
+ ("DATE", "DATE '2019-01-02'"),
+ ("DateTime", "TIMESTAMP '2019-01-02 03:04:05.678900'"),
+ ("TimeStamp", "TIMESTAMP '2019-01-02 03:04:05.678900'"),
+ ("TIMESTAMP", "TIMESTAMP '2019-01-02 03:04:05.678900'"),
("UnknownType", None),
],
)
@@ -57,34 +53,28 @@ def test_epoch_to_dttm() -> None:
("PT1S", "DATE_TRUNC('second', ts)"),
(
"PT5S",
- "DATE_TRUNC('minute', ts) + INTERVAL '5 seconds' * "
- "FLOOR(EXTRACT(SECOND FROM ts) / 5)",
+ "DATE_BIN(INTERVAL '5 seconds', ts, TIMESTAMP '1970-01-01
00:00:00')",
),
(
"PT30S",
- "DATE_TRUNC('minute', ts) + INTERVAL '30 seconds' * "
- "FLOOR(EXTRACT(SECOND FROM ts) / 30)",
+ "DATE_BIN(INTERVAL '30 seconds', ts, TIMESTAMP '1970-01-01
00:00:00')",
),
("PT1M", "DATE_TRUNC('minute', ts)"),
(
"PT5M",
- "DATE_TRUNC('hour', ts) + INTERVAL '5 minutes' * "
- "FLOOR(EXTRACT(MINUTE FROM ts) / 5)",
+ "DATE_BIN(INTERVAL '5 minutes', ts, TIMESTAMP '1970-01-01
00:00:00')",
),
(
"PT10M",
- "DATE_TRUNC('hour', ts) + INTERVAL '10 minutes' * "
- "FLOOR(EXTRACT(MINUTE FROM ts) / 10)",
+ "DATE_BIN(INTERVAL '10 minutes', ts, TIMESTAMP '1970-01-01
00:00:00')",
),
(
"PT15M",
- "DATE_TRUNC('hour', ts) + INTERVAL '15 minutes' * "
- "FLOOR(EXTRACT(MINUTE FROM ts) / 15)",
+ "DATE_BIN(INTERVAL '15 minutes', ts, TIMESTAMP '1970-01-01
00:00:00')",
),
(
"PT30M",
- "DATE_TRUNC('hour', ts) + INTERVAL '30 minutes' * "
- "FLOOR(EXTRACT(MINUTE FROM ts) / 30)",
+ "DATE_BIN(INTERVAL '30 minutes', ts, TIMESTAMP '1970-01-01
00:00:00')",
),
("PT1H", "DATE_TRUNC('hour', ts)"),
("P1D", "DATE_TRUNC('day', ts)"),
