seunggabi opened a new pull request, #44500:
URL: https://github.com/apache/superset/pull/44500
### SUMMARY
Dataset columns can declare that an integer holds a Unix epoch via the
*Datetime format* field, but only `epoch_s` and `epoch_ms` are understood.
Microsecond epochs are just as common in modern warehouses — Debezium emits
`MicroTimestamp` (µs) for `DATETIME(4-6)` sources under its default `adaptive*`
precision modes, Avro `timestamp-micros`, Parquet `TIMESTAMP_MICROS` and
Iceberg timestamps are all µs. For such a column the only options today are a
calculated column (which turns the raw column comparison into an expression and
loses pruning on the source column) or leaving the format empty, in which case
the time range filter is rendered as a string literal and the warehouse rejects
it:
```
TYPE_MISMATCH: Cannot apply operator: bigint <= varchar(26)
```
This PR adds `epoch_us` next to the existing formats, mirroring `epoch_ms`
in every place it is handled:
- **Filter literals** (`ExploreMixin.dttm_sql_literal`): UTC seconds × 1 000
000.
- **Time grain expressions** (`BaseEngineSpec.get_timestamp_expr`): new
`epoch_us_to_dttm()` classmethod, defaulting to `epoch_to_dttm()` applied to
`({col}/1000000)` so every engine spec that implements `epoch_to_dttm` gets it
for free; engines can override like they do for `epoch_ms_to_dttm`.
- **Result normalization** (`utils.core._process_datetime_column`,
`_retry_temporal_join_values_at_wider_resolution`): pandas unit `us`.
- **Validation** (`datasets.schemas.validate_python_date_format`,
`DatasetDAO.validate_python_date_format`): accept the value.
- **Dataset editor help text** mentions the new value.
The hardcoded `("epoch_s", "epoch_ms")` tuples scattered across these
modules are replaced by a single `EPOCH_FORMATS` constant (`{"epoch_s": 1,
"epoch_ms": 1_000, "epoch_us": 1_000_000}`) so the formats can't drift apart
again. No behaviour change for `epoch_s` / `epoch_ms`.
Generated SQL for an Athena/Trino BIGINT µs column with a daily grain and a
time range:
```sql
SELECT date_trunc('day', CAST(from_unixtime((created_at/1000000)) AS
TIMESTAMP)) AS created_at, ...
FROM app_community_receipt
WHERE created_at >= 1789344000000000 AND created_at < 1789948800000000
```
### BEFORE/AFTER SCREENSHOTS OR ANIMATED GIF
N/A (help text change only in the UI).
### TESTING INSTRUCTIONS
1. Create a physical dataset on a table with a BIGINT column storing
microsecond epochs (e.g. a Debezium-fed Iceberg/Trino/Athena table, or `SELECT
1704067200000000 AS ts`).
2. Edit the column: check *Is temporal*, set *Datetime format* to
`epoch_us`, save.
3. Build a time-series chart on that column with a time range filter and a
daily grain — the query renders the filter as an integer literal and the grain
via `from_unixtime(col/1000000)`; the x-axis shows the expected dates.
Unit tests added/extended:
- `tests/unit_tests/models/core_test.py` — `dttm_sql_literal` for `epoch_us`
- `tests/unit_tests/db_engine_specs/test_athena.py` — `get_timestamp_expr`
for all three epoch formats, with and without grain
- `tests/unit_tests/utils/test_core.py` — `normalize_dttm_col` for
microseconds
- `tests/unit_tests/datasets/schema_tests.py` — `epoch_us` accepted by the
validator
### ADDITIONAL INFORMATION
- [ ] Has associated issue:
- [ ] Required feature flags:
- [x] Changes UI
- [ ] Includes DB Migration (follow approval process in
[SIP-59](https://github.com/apache/superset/issues/13351))
- [ ] Migration is atomic, supports rollback & is backwards-compatible
- [ ] Confirm DB migration upgrade and downgrade tested
- [ ] Runtime estimates and downtime expectations provided
- [x] Introduces new feature or API
- [ ] Removes existing feature or API
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
