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 775f7c5a1fd fix(db2): Use DATE_TRUNC to improve timegrain expressions
(#43236)
775f7c5a1fd is described below
commit 775f7c5a1fd976e10e6f12f8be44f8a5350e3138
Author: Hong Ng <[email protected]>
AuthorDate: Thu Sep 24 06:21:47 2026 +1000
fix(db2): Use DATE_TRUNC to improve timegrain expressions (#43236)
Co-authored-by: Hong Yang Ng <[email protected]>
Co-authored-by: Evan Rusackas <[email protected]>
---
UPDATING.md | 10 ++++
superset/db_engine_specs/db2.py | 27 ++++------
superset/db_engine_specs/ibmi.py | 21 ++++++++
tests/unit_tests/db_engine_specs/test_db2.py | 35 +++---------
tests/unit_tests/db_engine_specs/test_ibmi.py | 76 +++++++++++++++++++++++++++
5 files changed, 126 insertions(+), 43 deletions(-)
diff --git a/UPDATING.md b/UPDATING.md
index aa6a0b7dcbf..ba734724776 100644
--- a/UPDATING.md
+++ b/UPDATING.md
@@ -199,6 +199,16 @@ Scheduled report and alert captures require chart
readiness to remain stable
immediately before Chromium captures the image. A capture that re-enters a
loading
state during that window fails instead of delivering a screenshot with
spinners.
+### Improve Db2 Time Grain Expressions
+The Db2 engine spec has been streamlined by using the DATE_TRUNC scalar
function,
+which requires Db2 11.1.0 or higher. Per the ISO 8601 standards, the `WEEK`
time
+grain now shifts the first day of the week to Monday as part of this change.
+
+### Update IBM Db2 for i Time Grain Expressions
+IBM Db2 for i inherits its engine spec from Db2 but does not support the
DATE_TRUNC
+scalar function, so it will use the previous arithmetic expressions defined
for Db2.
+Its `WEEK` time grain now uses `DAYOFWEEK_ISO` to align with the Db2 change.
+
### Scheduled rendered reports fail closed after capture rejection
Scheduled PDF and PNG delivery requires an accepted report capture context. A
diff --git a/superset/db_engine_specs/db2.py b/superset/db_engine_specs/db2.py
index 33b02130ae7..dc66dc31923 100644
--- a/superset/db_engine_specs/db2.py
+++ b/superset/db_engine_specs/db2.py
@@ -57,7 +57,9 @@ class Db2EngineSpec(BaseEngineSpec):
"connection_string":
"ibm_db_sa://{username}:{password}@{hostname}:{port}/{database}",
"is_recommended": False,
"notes": (
- "Use for older DB2 versions without LIMIT [n] syntax. "
+ "Db2 11.1.0 or higher is required to support SQL
compatibility "
+ "enhancements. "
+ "Use for older Db2 versions without LIMIT [n] syntax. "
"Recommended for SQL Lab."
),
},
@@ -95,21 +97,14 @@ class Db2EngineSpec(BaseEngineSpec):
_time_grain_expressions = {
None: "{col}",
- TimeGrain.SECOND: "CAST({col} as TIMESTAMP) - MICROSECOND({col})
MICROSECONDS",
- TimeGrain.MINUTE: "CAST({col} as TIMESTAMP)"
- " - SECOND({col}) SECONDS"
- " - MICROSECOND({col}) MICROSECONDS",
- TimeGrain.HOUR: "CAST({col} as TIMESTAMP)"
- " - MINUTE({col}) MINUTES"
- " - SECOND({col}) SECONDS"
- " - MICROSECOND({col}) MICROSECONDS ",
- TimeGrain.DAY: "DATE({col})",
- TimeGrain.WEEK: "{col} - (DAYOFWEEK({col})) DAYS",
- TimeGrain.MONTH: "{col} - (DAY({col})-1) DAYS",
- TimeGrain.QUARTER: "{col} - (DAY({col})-1) DAYS"
- " - (MONTH({col})-1) MONTHS"
- " + ((QUARTER({col})-1) * 3) MONTHS",
- TimeGrain.YEAR: "{col} - (DAY({col})-1) DAYS - (MONTH({col})-1)
MONTHS",
+ TimeGrain.SECOND: "DATE_TRUNC('SECOND', {col})",
+ TimeGrain.MINUTE: "DATE_TRUNC('MINUTE', {col})",
+ TimeGrain.HOUR: "DATE_TRUNC('HOUR', {col})",
+ TimeGrain.DAY: "DATE_TRUNC('DAY', {col})",
+ TimeGrain.WEEK: "DATE_TRUNC('WEEK', {col})",
+ TimeGrain.MONTH: "DATE_TRUNC('MONTH', {col})",
+ TimeGrain.QUARTER: "DATE_TRUNC('QUARTER', {col})",
+ TimeGrain.YEAR: "DATE_TRUNC('YEAR', {col})",
}
@classmethod
diff --git a/superset/db_engine_specs/ibmi.py b/superset/db_engine_specs/ibmi.py
index 481c714d793..35756709cc9 100644
--- a/superset/db_engine_specs/ibmi.py
+++ b/superset/db_engine_specs/ibmi.py
@@ -14,6 +14,8 @@
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
+from superset.constants import TimeGrain
+
from .db2 import Db2EngineSpec
@@ -28,6 +30,25 @@ class IBMiEngineSpec(Db2EngineSpec):
engine_name = "IBM Db2 for i"
max_column_name_length = 128
+ _time_grain_expressions = {
+ None: "{col}",
+ TimeGrain.SECOND: "CAST({col} as TIMESTAMP) - MICROSECOND({col})
MICROSECONDS",
+ TimeGrain.MINUTE: "CAST({col} as TIMESTAMP)"
+ " - SECOND({col}) SECONDS"
+ " - MICROSECOND({col}) MICROSECONDS",
+ TimeGrain.HOUR: "CAST({col} as TIMESTAMP)"
+ " - MINUTE({col}) MINUTES"
+ " - SECOND({col}) SECONDS"
+ " - MICROSECOND({col}) MICROSECONDS ",
+ TimeGrain.DAY: "DATE({col})",
+ TimeGrain.WEEK: "{col} - (DAYOFWEEK_ISO({col})-1) DAYS",
+ TimeGrain.MONTH: "{col} - (DAY({col})-1) DAYS",
+ TimeGrain.QUARTER: "{col} - (DAY({col})-1) DAYS"
+ " - (MONTH({col})-1) MONTHS"
+ " + ((QUARTER({col})-1) * 3) MONTHS",
+ TimeGrain.YEAR: "{col} - (DAY({col})-1) DAYS - (MONTH({col})-1)
MONTHS",
+ }
+
@classmethod
def epoch_to_dttm(cls) -> str:
return "(DAYS({col}) - DAYS('1970-01-01')) * 86400 +
MIDNIGHT_SECONDS({col})"
diff --git a/tests/unit_tests/db_engine_specs/test_db2.py
b/tests/unit_tests/db_engine_specs/test_db2.py
index 2d2421449b1..ad87c4fc737 100644
--- a/tests/unit_tests/db_engine_specs/test_db2.py
+++ b/tests/unit_tests/db_engine_specs/test_db2.py
@@ -109,33 +109,14 @@ def test_get_prequeries(mocker: MockerFixture) -> None:
("grain", "expected_expression"),
[
(None, "my_col"),
- (
- TimeGrain.SECOND,
- "CAST(my_col as TIMESTAMP) - MICROSECOND(my_col) MICROSECONDS",
- ),
- (
- TimeGrain.MINUTE,
- "CAST(my_col as TIMESTAMP)"
- " - SECOND(my_col) SECONDS - MICROSECOND(my_col) MICROSECONDS",
- ),
- (
- TimeGrain.HOUR,
- "CAST(my_col as TIMESTAMP)"
- " - MINUTE(my_col) MINUTES"
- " - SECOND(my_col) SECONDS - MICROSECOND(my_col) MICROSECONDS ",
- ),
- (TimeGrain.DAY, "DATE(my_col)"),
- (TimeGrain.WEEK, "my_col - (DAYOFWEEK(my_col)) DAYS"),
- (TimeGrain.MONTH, "my_col - (DAY(my_col)-1) DAYS"),
- (
- TimeGrain.QUARTER,
- "my_col - (DAY(my_col)-1) DAYS"
- " - (MONTH(my_col)-1) MONTHS + ((QUARTER(my_col)-1) * 3) MONTHS",
- ),
- (
- TimeGrain.YEAR,
- "my_col - (DAY(my_col)-1) DAYS - (MONTH(my_col)-1) MONTHS",
- ),
+ (TimeGrain.SECOND, "DATE_TRUNC('SECOND', my_col)"),
+ (TimeGrain.MINUTE, "DATE_TRUNC('MINUTE', my_col)"),
+ (TimeGrain.HOUR, "DATE_TRUNC('HOUR', my_col)"),
+ (TimeGrain.DAY, "DATE_TRUNC('DAY', my_col)"),
+ (TimeGrain.WEEK, "DATE_TRUNC('WEEK', my_col)"),
+ (TimeGrain.MONTH, "DATE_TRUNC('MONTH', my_col)"),
+ (TimeGrain.QUARTER, "DATE_TRUNC('QUARTER', my_col)"),
+ (TimeGrain.YEAR, "DATE_TRUNC('YEAR', my_col)"),
],
)
def test_time_grain_expressions(grain: TimeGrain, expected_expression: str) ->
None:
diff --git a/tests/unit_tests/db_engine_specs/test_ibmi.py
b/tests/unit_tests/db_engine_specs/test_ibmi.py
new file mode 100644
index 00000000000..e71f96ddf1c
--- /dev/null
+++ b/tests/unit_tests/db_engine_specs/test_ibmi.py
@@ -0,0 +1,76 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+
+import pytest
+
+from superset.constants import TimeGrain
+
+
+def test_epoch_to_dttm() -> None:
+ """
+ Test the `epoch_to_dttm` method.
+ """
+ from superset.db_engine_specs.ibmi import IBMiEngineSpec
+
+ assert (
+ IBMiEngineSpec.epoch_to_dttm().format(col="epoch_dttm")
+ == "(DAYS(epoch_dttm) - DAYS('1970-01-01')) * 86400"
+ " + MIDNIGHT_SECONDS(epoch_dttm)"
+ )
+
+
[email protected](
+ ("grain", "expected_expression"),
+ [
+ (None, "my_col"),
+ (
+ TimeGrain.SECOND,
+ "CAST(my_col as TIMESTAMP) - MICROSECOND(my_col) MICROSECONDS",
+ ),
+ (
+ TimeGrain.MINUTE,
+ "CAST(my_col as TIMESTAMP)"
+ " - SECOND(my_col) SECONDS - MICROSECOND(my_col) MICROSECONDS",
+ ),
+ (
+ TimeGrain.HOUR,
+ "CAST(my_col as TIMESTAMP)"
+ " - MINUTE(my_col) MINUTES"
+ " - SECOND(my_col) SECONDS - MICROSECOND(my_col) MICROSECONDS ",
+ ),
+ (TimeGrain.DAY, "DATE(my_col)"),
+ (TimeGrain.WEEK, "my_col - (DAYOFWEEK_ISO(my_col)-1) DAYS"),
+ (TimeGrain.MONTH, "my_col - (DAY(my_col)-1) DAYS"),
+ (
+ TimeGrain.QUARTER,
+ "my_col - (DAY(my_col)-1) DAYS"
+ " - (MONTH(my_col)-1) MONTHS + ((QUARTER(my_col)-1) * 3) MONTHS",
+ ),
+ (
+ TimeGrain.YEAR,
+ "my_col - (DAY(my_col)-1) DAYS - (MONTH(my_col)-1) MONTHS",
+ ),
+ ],
+)
+def test_time_grain_expressions(grain: TimeGrain, expected_expression: str) ->
None:
+ """
+ Test that time grain expressions generate the expected SQL.
+ """
+ from superset.db_engine_specs.ibmi import IBMiEngineSpec
+
+ actual = IBMiEngineSpec._time_grain_expressions[grain].format(col="my_col")
+ assert actual == expected_expression
