You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ma...@apache.org on 2025/11/25 22:25:31 UTC
(superset) branch master updated: feat(datasets): add datetime format detection to dataset columns (#36150)
This is an automated email from the ASF dual-hosted git repository.
maximebeauchemin 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 06a8f4df02 feat(datasets): add datetime format detection to dataset columns (#36150)
06a8f4df02 is described below
commit 06a8f4df02818a5f075665c2fb70ce9e990a6c6c
Author: Amin Ghadersohi <am...@gmail.com>
AuthorDate: Wed Nov 26 09:25:24 2025 +1100
feat(datasets): add datetime format detection to dataset columns (#36150)
Co-authored-by: Claude Code <cl...@anthropic.com>
---
superset/commands/dataset/refresh.py | 18 ++
superset/config.py | 7 +
superset/connectors/sqla/models.py | 13 ++
superset/datasets/api.py | 82 ++++++++
superset/datasets/datetime_format_detector.py | 211 +++++++++++++++++++
superset/datasets/schemas.py | 3 +
...ec10479_add_datetime_format_to_table_columns.py | 68 +++++++
superset/models/helpers.py | 8 +
superset/utils/core.py | 14 ++
tests/integration_tests/datasets/commands_tests.py | 3 +
tests/unit_tests/datasets/commands/export_test.py | 5 +
.../datasets/test_datetime_format_detector.py | 225 +++++++++++++++++++++
12 files changed, 657 insertions(+)
diff --git a/superset/commands/dataset/refresh.py b/superset/commands/dataset/refresh.py
index 9605ac866a..f33fcc2793 100644
--- a/superset/commands/dataset/refresh.py
+++ b/superset/commands/dataset/refresh.py
@@ -18,6 +18,7 @@ import logging
from functools import partial
from typing import Optional
+from flask import current_app
from flask_appbuilder.models.sqla import Model
from superset import security_manager
@@ -29,6 +30,7 @@ from superset.commands.dataset.exceptions import (
)
from superset.connectors.sqla.models import SqlaTable
from superset.daos.dataset import DatasetDAO
+from superset.datasets.datetime_format_detector import DatetimeFormatDetector
from superset.exceptions import SupersetSecurityException
from superset.utils.decorators import on_error, transaction
@@ -45,6 +47,22 @@ class RefreshDatasetCommand(BaseCommand):
self.validate()
assert self._model
self._model.fetch_metadata()
+
+ # Detect datetime formats if feature is enabled
+ if current_app.config.get("DATASET_AUTO_DETECT_DATETIME_FORMATS", True):
+ try:
+ detector = DatetimeFormatDetector()
+ detector.detect_all_formats(self._model)
+ logger.info(
+ "Detected datetime formats for dataset %s", self._model.table_name
+ )
+ except Exception as ex:
+ logger.exception(
+ "Failed to detect datetime formats for dataset %s: %s",
+ self._model.table_name,
+ str(ex),
+ )
+
return self._model
def validate(self) -> None:
diff --git a/superset/config.py b/superset/config.py
index 3e655949d7..2d08a952de 100644
--- a/superset/config.py
+++ b/superset/config.py
@@ -1131,6 +1131,13 @@ DISPLAY_MAX_ROW = 10000
# the SQL Lab UI
DEFAULT_SQLLAB_LIMIT = 1000
+# Dataset datetime format detection settings
+# Auto-detect datetime formats on dataset creation/refresh
+DATASET_AUTO_DETECT_DATETIME_FORMATS = True
+
+# Sample size for datetime format detection
+DATETIME_FORMAT_DETECTION_SAMPLE_SIZE = 1000
+
# The limit for the Superset Meta DB when the feature flag ENABLE_SUPERSET_META_DB is on
SUPERSET_META_DB_LIMIT: int | None = 1000
diff --git a/superset/connectors/sqla/models.py b/superset/connectors/sqla/models.py
index 39bc716b6d..a5ae662384 100644
--- a/superset/connectors/sqla/models.py
+++ b/superset/connectors/sqla/models.py
@@ -775,6 +775,7 @@ class TableColumn(AuditMixinNullable, ImportExportMixin, CertificationMixin, Mod
is_dttm = Column(Boolean, default=False)
expression = Column(utils.MediumText())
python_date_format = Column(String(255))
+ datetime_format = Column(String(100))
extra = Column(Text)
table: Mapped[SqlaTable] = relationship(
@@ -795,6 +796,7 @@ class TableColumn(AuditMixinNullable, ImportExportMixin, CertificationMixin, Mod
"expression",
"description",
"python_date_format",
+ "datetime_format",
"extra",
]
@@ -861,6 +863,17 @@ class TableColumn(AuditMixinNullable, ImportExportMixin, CertificationMixin, Mod
return self.is_dttm
return self.type_generic == utils.GenericDataType.TEMPORAL
+ @property
+ def effective_datetime_format(self) -> str | None:
+ """
+ Get the datetime format for this column with fallback logic.
+
+ Returns the stored datetime_format if available. This format is detected
+ during dataset creation/sync and used for consistent datetime parsing.
+ Falls back to None if no format is stored, triggering runtime detection.
+ """
+ return self.datetime_format
+
@property
def database(self) -> Database:
return self.table.database if self.table else self._database # type: ignore
diff --git a/superset/datasets/api.py b/superset/datasets/api.py
index 92589309e1..14e5464d8d 100644
--- a/superset/datasets/api.py
+++ b/superset/datasets/api.py
@@ -705,6 +705,88 @@ class DatasetRestApi(BaseSupersetModelRestApi):
)
return self.response_422(message=str(ex))
+ @expose("/<pk>/detect_datetime_formats", methods=("POST",))
+ @protect()
+ @safe
+ @statsd_metrics
+ @event_logger.log_this_with_context(
+ action=lambda self, *args, **kwargs: f"{self.__class__.__name__}"
+ ".detect_datetime_formats",
+ log_to_statsd=False,
+ )
+ def detect_datetime_formats(self, pk: int) -> Response:
+ """Detect and store datetime formats for dataset columns.
+ ---
+ post:
+ summary: Detect datetime formats for dataset columns
+ parameters:
+ - in: path
+ schema:
+ type: integer
+ name: pk
+ - in: query
+ schema:
+ type: boolean
+ name: force
+ description: Force re-detection even if formats already exist
+ responses:
+ 200:
+ description: Datetime formats detected
+ content:
+ application/json:
+ schema:
+ type: object
+ properties:
+ message:
+ type: string
+ formats:
+ type: object
+ additionalProperties:
+ type: string
+ 401:
+ $ref: '#/components/responses/401'
+ 403:
+ $ref: '#/components/responses/403'
+ 404:
+ $ref: '#/components/responses/404'
+ 500:
+ $ref: '#/components/responses/500'
+ """
+ # pylint: disable=import-outside-toplevel
+ from superset.datasets.datetime_format_detector import DatetimeFormatDetector
+
+ try:
+ # Get force parameter from query string
+ force = parse_boolean_string(request.args.get("force", "false"))
+
+ # Get dataset
+ dataset = DatasetDAO.find_by_id(pk)
+ if not dataset:
+ return self.response_404()
+
+ # Check ownership
+ try:
+ security_manager.raise_for_ownership(dataset)
+ except Exception: # pylint: disable=broad-except
+ return self.response_403()
+
+ # Detect formats
+ detector = DatetimeFormatDetector()
+ formats = detector.detect_all_formats(dataset, force=force)
+
+ return self.response(
+ 200,
+ message="Datetime formats detected successfully",
+ formats=formats,
+ )
+ except Exception as ex:
+ logger.exception(
+ "Error detecting datetime formats for dataset %s: %s",
+ pk,
+ str(ex),
+ )
+ return self.response_500(message=str(ex))
+
@expose("/<id_or_uuid>/related_objects", methods=("GET",))
@protect()
@safe
diff --git a/superset/datasets/datetime_format_detector.py b/superset/datasets/datetime_format_detector.py
new file mode 100644
index 0000000000..a1f751e75e
--- /dev/null
+++ b/superset/datasets/datetime_format_detector.py
@@ -0,0 +1,211 @@
+# 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.
+"""Service for detecting datetime formats in dataset columns."""
+
+from __future__ import annotations
+
+import logging
+from typing import TYPE_CHECKING
+
+from flask import current_app
+
+from superset.connectors.sqla.models import SqlaTable, TableColumn
+from superset.utils.decorators import transaction
+from superset.utils.pandas import detect_datetime_format
+
+if TYPE_CHECKING:
+ from superset.models.core import Database
+
+logger = logging.getLogger(__name__)
+
+
+class DatetimeFormatDetector:
+ """
+ Service for detecting and storing datetime formats in dataset columns.
+
+ This service samples data from datetime columns to detect their format,
+ reducing the need for runtime format detection on every query.
+ """
+
+ def __init__(self, sample_size: int | None = None) -> None:
+ """
+ Initialize the datetime format detector.
+
+ :param sample_size: Number of rows to sample for format detection
+ """
+ self.sample_size = sample_size or current_app.config.get(
+ "DATETIME_FORMAT_DETECTION_SAMPLE_SIZE", 1000
+ )
+
+ def detect_column_format(
+ self,
+ dataset: SqlaTable,
+ column: TableColumn,
+ ) -> str | None:
+ """
+ Detect datetime format for a specific column.
+
+ :param dataset: The dataset containing the column
+ :param column: The column to detect format for
+ :return: Detected format string or None if detection fails
+ """
+ if not column.is_temporal:
+ logger.debug(
+ "Column %s is not temporal, skipping format detection",
+ column.column_name,
+ )
+ return None
+
+ # Skip expression columns - they don't have stored data to sample
+ if column.expression:
+ logger.debug(
+ "Column %s is an expression column, skipping format detection",
+ column.column_name,
+ )
+ return None
+
+ # Skip virtual datasets - they use SQL queries, not physical tables
+ if dataset.is_virtual:
+ logger.debug(
+ "Dataset %s is virtual, skipping format detection for column %s",
+ dataset.table_name,
+ column.column_name,
+ )
+ return None
+
+ try:
+ # Build SQL query using database's identifier quoting
+ # Note: Column and table names come from internal metadata, not user input
+ database: Database = dataset.database
+
+ # Get the database engine's dialect for proper identifier quoting
+ with database.get_sqla_engine() as engine:
+ dialect = engine.dialect
+
+ # Quote identifiers using the dialect's identifier preparer
+ column_name_quoted = dialect.identifier_preparer.quote(
+ column.column_name
+ )
+ table_name_quoted = dialect.identifier_preparer.quote(
+ dataset.table_name
+ )
+
+ if dataset.schema:
+ schema_quoted = dialect.identifier_preparer.quote(dataset.schema)
+ full_table = f"{schema_quoted}.{table_name_quoted}"
+ else:
+ full_table = table_name_quoted
+
+ # Build SQL query string with quoted identifiers
+ # S608: false positive - using dialect's identifier preparer
+ sql = ( # noqa: S608
+ f"SELECT {column_name_quoted} FROM {full_table} " # noqa: S608
+ f"WHERE {column_name_quoted} IS NOT NULL" # noqa: S608
+ )
+
+ # Apply database-specific LIMIT using apply_limit_to_sql
+ # This handles different SQL dialects (LIMIT, TOP, FETCH FIRST, etc.)
+ sql = database.apply_limit_to_sql(sql, limit=self.sample_size, force=True)
+
+ # Execute query and get results
+ df = database.get_df(sql, dataset.schema)
+
+ if df.empty or column.column_name not in df.columns:
+ logger.warning(
+ "No data returned for column %s in dataset %s",
+ column.column_name,
+ dataset.table_name,
+ )
+ return None
+
+ # Detect format using existing utility
+ series = df[column.column_name]
+ detected_format = detect_datetime_format(series, self.sample_size)
+
+ if detected_format:
+ logger.info(
+ "Detected format '%s' for column %s.%s",
+ detected_format,
+ dataset.table_name,
+ column.column_name,
+ )
+ else:
+ logger.warning(
+ "Could not detect format for column %s.%s",
+ dataset.table_name,
+ column.column_name,
+ )
+
+ return detected_format
+
+ except Exception as ex:
+ logger.exception(
+ "Error detecting format for column %s.%s: %s",
+ dataset.table_name,
+ column.column_name,
+ str(ex),
+ )
+ return None
+
+ @transaction()
+ def detect_all_formats(
+ self,
+ dataset: SqlaTable,
+ force: bool = False,
+ ) -> dict[str, str | None]:
+ """
+ Detect datetime formats for all temporal columns in a dataset.
+
+ :param dataset: The dataset to process
+ :param force: If True, re-detect even if format already exists
+ :return: Dictionary mapping column names to detected formats
+ """
+ results: dict[str, str | None] = {}
+
+ for column in dataset.columns:
+ # Skip if not temporal
+ if not column.is_temporal:
+ continue
+
+ # Skip if format already exists and not forcing re-detection
+ if column.datetime_format and not force:
+ logger.debug(
+ "Column %s.%s already has format '%s', skipping",
+ dataset.table_name,
+ column.column_name,
+ column.datetime_format,
+ )
+ results[column.column_name] = column.datetime_format
+ continue
+
+ # Detect and store format
+ detected_format = self.detect_column_format(dataset, column)
+ if detected_format:
+ column.datetime_format = detected_format
+ results[column.column_name] = detected_format
+ else:
+ results[column.column_name] = None
+
+ # Log results
+ if results:
+ logger.info(
+ "Detected formats for %d columns in dataset %s",
+ len(results),
+ dataset.table_name,
+ )
+
+ return results
diff --git a/superset/datasets/schemas.py b/superset/datasets/schemas.py
index 431881d194..e49abd3f4d 100644
--- a/superset/datasets/schemas.py
+++ b/superset/datasets/schemas.py
@@ -86,6 +86,9 @@ class DatasetColumnsPutSchema(Schema):
python_date_format = fields.String(
allow_none=True, validate=[Length(1, 255), validate_python_date_format]
)
+ datetime_format = fields.String(
+ allow_none=True, validate=[Length(1, 100), validate_python_date_format]
+ )
uuid = fields.UUID(allow_none=True)
diff --git a/superset/migrations/versions/2025-11-17_19-03_a9c01ec10479_add_datetime_format_to_table_columns.py b/superset/migrations/versions/2025-11-17_19-03_a9c01ec10479_add_datetime_format_to_table_columns.py
new file mode 100644
index 0000000000..2a6813f3eb
--- /dev/null
+++ b/superset/migrations/versions/2025-11-17_19-03_a9c01ec10479_add_datetime_format_to_table_columns.py
@@ -0,0 +1,68 @@
+# 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.
+"""add_datetime_format_to_table_columns
+
+Revision ID: a9c01ec10479
+Revises: x2s8ocx6rto6
+Create Date: 2025-11-17 19:03:48.600000
+
+"""
+
+import logging
+
+import sqlalchemy as sa
+from alembic import op
+
+from superset.migrations.shared.utils import get_table_column
+
+logger = logging.getLogger("alembic.env")
+
+# revision identifiers, used by Alembic.
+revision = "a9c01ec10479"
+down_revision = "x2s8ocx6rto6"
+
+
+def upgrade():
+ """Add datetime_format column to table_columns table."""
+ # Check if column already exists to avoid errors if already upgraded
+ column_info = get_table_column("table_columns", "datetime_format")
+
+ if column_info is not None:
+ logger.info(
+ "Column table_columns.datetime_format already exists. Skipping migration."
+ )
+ return
+
+ with op.batch_alter_table("table_columns") as batch_op:
+ batch_op.add_column(
+ sa.Column("datetime_format", sa.String(length=100), nullable=True)
+ )
+
+
+def downgrade():
+ """Remove datetime_format column from table_columns table."""
+ # Check if column exists before attempting to drop it
+ column_info = get_table_column("table_columns", "datetime_format")
+
+ if column_info is None:
+ logger.info(
+ "Column table_columns.datetime_format does not exist. Skipping downgrade."
+ )
+ return
+
+ with op.batch_alter_table("table_columns") as batch_op:
+ batch_op.drop_column("datetime_format")
diff --git a/superset/models/helpers.py b/superset/models/helpers.py
index ecf1ff869c..2f3208c820 100644
--- a/superset/models/helpers.py
+++ b/superset/models/helpers.py
@@ -1275,9 +1275,17 @@ class ExploreMixin: # pylint: disable=too-many-public-methods
)
)
+ # Build format map from detected datetime formats stored in dataset columns
+ format_map: dict[str, str] = {}
+ if hasattr(self, "columns"):
+ for col in self.columns:
+ if hasattr(col, "datetime_format") and col.datetime_format:
+ format_map[col.column_name] = col.datetime_format
+
normalize_dttm_col(
df=df,
dttm_cols=tuple(dttm_cols),
+ format_map=format_map if format_map else None,
)
# Convert metrics to numerical values if enforced
diff --git a/superset/utils/core.py b/superset/utils/core.py
index 9acd7e03c7..8fd7e4750e 100644
--- a/superset/utils/core.py
+++ b/superset/utils/core.py
@@ -1927,11 +1927,25 @@ def _process_datetime_column(
def normalize_dttm_col(
df: pd.DataFrame,
dttm_cols: tuple[DateColumn, ...] = tuple(), # noqa: C408
+ format_map: dict[str, str] | None = None,
) -> None:
+ """
+ Normalize datetime columns in a DataFrame.
+
+ :param df: DataFrame to process
+ :param dttm_cols: Tuple of DateColumn objects to process
+ :param format_map: Optional mapping of column names to datetime formats.
+ When provided, these pre-detected formats are used instead
+ of runtime detection, improving performance and consistency.
+ """
for _col in dttm_cols:
if _col.col_label not in df.columns:
continue
+ # Use format from format_map if available and not already set
+ if format_map and _col.col_label in format_map and not _col.timestamp_format:
+ _col.timestamp_format = format_map[_col.col_label]
+
_process_datetime_column(df, _col)
if _col.offset:
diff --git a/tests/integration_tests/datasets/commands_tests.py b/tests/integration_tests/datasets/commands_tests.py
index 9f4cf6395d..92a0e37179 100644
--- a/tests/integration_tests/datasets/commands_tests.py
+++ b/tests/integration_tests/datasets/commands_tests.py
@@ -101,6 +101,7 @@ class TestExportDatasetsCommand(SupersetTestCase):
"columns": [
{
"column_name": "source",
+ "datetime_format": None,
"description": None,
"expression": "",
"filterable": True,
@@ -115,6 +116,7 @@ class TestExportDatasetsCommand(SupersetTestCase):
},
{
"column_name": "target",
+ "datetime_format": None,
"description": None,
"expression": "",
"filterable": True,
@@ -129,6 +131,7 @@ class TestExportDatasetsCommand(SupersetTestCase):
},
{
"column_name": "value",
+ "datetime_format": None,
"description": None,
"expression": "",
"filterable": True,
diff --git a/tests/unit_tests/datasets/commands/export_test.py b/tests/unit_tests/datasets/commands/export_test.py
index 47d72bf9d4..13b114c99a 100644
--- a/tests/unit_tests/datasets/commands/export_test.py
+++ b/tests/unit_tests/datasets/commands/export_test.py
@@ -221,6 +221,7 @@ columns:
expression: revenue-expenses
description: null
python_date_format: null
+ datetime_format: null
extra:
certified_by: User
- column_name: ds
@@ -234,6 +235,7 @@ columns:
expression: null
description: null
python_date_format: null
+ datetime_format: null
extra: null
- column_name: user_id
verbose_name: null
@@ -246,6 +248,7 @@ columns:
expression: null
description: null
python_date_format: null
+ datetime_format: null
extra: null
- column_name: expenses
verbose_name: null
@@ -258,6 +261,7 @@ columns:
expression: null
description: null
python_date_format: null
+ datetime_format: null
extra: null
- column_name: revenue
verbose_name: null
@@ -270,6 +274,7 @@ columns:
expression: null
description: null
python_date_format: null
+ datetime_format: null
extra: null
version: 1.0.0
database_uuid: {database.uuid}
diff --git a/tests/unit_tests/datasets/test_datetime_format_detector.py b/tests/unit_tests/datasets/test_datetime_format_detector.py
new file mode 100644
index 0000000000..7c1052fbe1
--- /dev/null
+++ b/tests/unit_tests/datasets/test_datetime_format_detector.py
@@ -0,0 +1,225 @@
+# 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.
+"""Tests for datetime format detector."""
+
+from unittest.mock import MagicMock
+
+import pandas as pd
+import pytest
+
+from superset.connectors.sqla.models import SqlaTable, TableColumn
+from superset.datasets.datetime_format_detector import DatetimeFormatDetector
+
+
+@pytest.fixture
+def mock_dataset() -> MagicMock:
+ """Create a mock dataset for testing."""
+ dataset = MagicMock(spec=SqlaTable)
+ dataset.table_name = "test_table"
+ dataset.schema = "test_schema"
+ dataset.is_virtual = False
+
+ # Mock the database engine and dialect for identifier quoting
+ mock_engine = MagicMock()
+ mock_dialect = MagicMock()
+ mock_dialect.identifier_preparer.quote = lambda x: f'"{x}"'
+ mock_engine.dialect = mock_dialect
+
+ # Mock the context manager returned by get_sqla_engine()
+ dataset.database.get_sqla_engine.return_value.__enter__.return_value = mock_engine
+ dataset.database.get_sqla_engine.return_value.__exit__.return_value = None
+
+ # Mock apply_limit_to_sql to return SQL with LIMIT
+ dataset.database.apply_limit_to_sql = (
+ lambda sql, limit, force: f"{sql} LIMIT {limit}"
+ )
+
+ return dataset
+
+
+@pytest.fixture
+def mock_column() -> MagicMock:
+ """Create a mock datetime column for testing."""
+ column = MagicMock(spec=TableColumn)
+ column.column_name = "date_column"
+ column.is_temporal = True
+ column.datetime_format = None
+ column.expression = None # Not an expression column
+ return column
+
+
+def test_detect_column_format_success(
+ mock_dataset: MagicMock, mock_column: MagicMock
+) -> None:
+ """Test successful format detection for a column."""
+ # Create sample data with known format
+ sample_data = pd.DataFrame(
+ {"date_column": ["2023-01-01", "2023-01-02", "2023-01-03"]}
+ )
+
+ # Mock database query
+ mock_dataset.database.get_df.return_value = sample_data
+
+ detector = DatetimeFormatDetector(sample_size=100)
+ detected_format = detector.detect_column_format(mock_dataset, mock_column)
+
+ assert detected_format == "%Y-%m-%d"
+ mock_dataset.database.get_df.assert_called_once()
+
+
+def test_detect_column_format_non_temporal(
+ mock_dataset: MagicMock, mock_column: MagicMock
+) -> None:
+ """Test that non-temporal columns are skipped."""
+ mock_column.is_temporal = False
+
+ detector = DatetimeFormatDetector()
+ detected_format = detector.detect_column_format(mock_dataset, mock_column)
+
+ assert detected_format is None
+ mock_dataset.database.get_df.assert_not_called()
+
+
+def test_detect_column_format_empty_data(
+ mock_dataset: MagicMock, mock_column: MagicMock
+) -> None:
+ """Test format detection with empty data."""
+ # Return empty DataFrame
+ mock_dataset.database.get_df.return_value = pd.DataFrame()
+
+ detector = DatetimeFormatDetector()
+ detected_format = detector.detect_column_format(mock_dataset, mock_column)
+
+ assert detected_format is None
+
+
+def test_detect_column_format_error_handling(
+ mock_dataset: MagicMock, mock_column: MagicMock
+) -> None:
+ """Test error handling during format detection."""
+ # Simulate database error
+ mock_dataset.database.get_df.side_effect = Exception("Database error")
+
+ detector = DatetimeFormatDetector()
+ detected_format = detector.detect_column_format(mock_dataset, mock_column)
+
+ assert detected_format is None
+
+
+def test_detect_all_formats(mock_dataset: MagicMock) -> None:
+ """Test detecting formats for all temporal columns."""
+ # Create mock columns
+ col1 = MagicMock(spec=TableColumn)
+ col1.column_name = "date1"
+ col1.is_temporal = True
+ col1.datetime_format = None
+ col1.expression = None
+
+ col2 = MagicMock(spec=TableColumn)
+ col2.column_name = "date2"
+ col2.is_temporal = True
+ col2.datetime_format = None
+ col2.expression = None
+
+ col3 = MagicMock(spec=TableColumn)
+ col3.column_name = "text_col"
+ col3.is_temporal = False
+ col3.expression = None
+
+ mock_dataset.columns = [col1, col2, col3]
+
+ # Mock database queries
+ sample_data1 = pd.DataFrame({"date1": ["2023-01-01", "2023-01-02"]})
+ sample_data2 = pd.DataFrame({"date2": ["01/15/2023", "01/16/2023"]})
+
+ mock_dataset.database.get_df.side_effect = [sample_data1, sample_data2]
+
+ detector = DatetimeFormatDetector()
+ results = detector.detect_all_formats(mock_dataset)
+
+ assert "date1" in results
+ assert "date2" in results
+ assert "text_col" not in results
+ assert results["date1"] == "%Y-%m-%d"
+ assert results["date2"] == "%m/%d/%Y"
+
+
+def test_detect_all_formats_skip_existing(mock_dataset: MagicMock) -> None:
+ """Test that columns with existing formats are skipped unless forced."""
+ # Create column with existing format
+ col1 = MagicMock(spec=TableColumn)
+ col1.column_name = "date1"
+ col1.is_temporal = True
+ col1.datetime_format = "%Y-%m-%d"
+ col1.expression = None
+
+ mock_dataset.columns = [col1]
+
+ detector = DatetimeFormatDetector()
+ results = detector.detect_all_formats(mock_dataset, force=False)
+
+ assert results["date1"] == "%Y-%m-%d"
+ mock_dataset.database.get_df.assert_not_called()
+
+
+def test_detect_all_formats_force_redetection(mock_dataset: MagicMock) -> None:
+ """Test forced re-detection of formats."""
+ # Create column with existing format
+ col1 = MagicMock(spec=TableColumn)
+ col1.column_name = "date1"
+ col1.is_temporal = True
+ col1.datetime_format = "%Y-%m-%d"
+ col1.expression = None
+
+ mock_dataset.columns = [col1]
+ mock_dataset.table_name = "test_table"
+
+ # Return different format
+ sample_data = pd.DataFrame({"date1": ["01/15/2023", "01/16/2023"]})
+ mock_dataset.database.get_df.return_value = sample_data
+
+ detector = DatetimeFormatDetector()
+ results = detector.detect_all_formats(mock_dataset, force=True)
+
+ assert results["date1"] == "%m/%d/%Y"
+ assert col1.datetime_format == "%m/%d/%Y"
+
+
+def test_detect_column_format_virtual_dataset(
+ mock_dataset: MagicMock, mock_column: MagicMock
+) -> None:
+ """Test that virtual datasets are skipped."""
+ mock_dataset.is_virtual = True
+
+ detector = DatetimeFormatDetector()
+ detected_format = detector.detect_column_format(mock_dataset, mock_column)
+
+ assert detected_format is None
+ mock_dataset.database.get_df.assert_not_called()
+
+
+def test_detect_column_format_expression_column(
+ mock_dataset: MagicMock, mock_column: MagicMock
+) -> None:
+ """Test that expression columns are skipped."""
+ mock_column.expression = "DATE_ADD(some_date, INTERVAL 1 DAY)"
+
+ detector = DatetimeFormatDetector()
+ detected_format = detector.detect_column_format(mock_dataset, mock_column)
+
+ assert detected_format is None
+ mock_dataset.database.get_df.assert_not_called()