fengruotj opened a new issue, #11219: URL: https://github.com/apache/paimon/issues/11219
### Search before asking - [x] I searched in the [issues](https://github.com/apache/paimon/issues) and found nothing similar. ### Paimon version 2.2-SNAPSHOT ### Compute Engine - Flink SQL (runtime version `1.17`) - Spark SQL (runtime version `3.5.10`). - JDK 11.0.25. ### Minimal reproduce step Read the same committed snapshot with different reader JVM time zones, independently of the SQL session time zone. 1. Start a local Flink SQL Client with the Paimon connector loaded and JVM option `-Duser.timezone=Asia/Shanghai`. Create a table and commit one row: ```sql SET 'execution.target' = 'local'; SET 'execution.runtime-mode' = 'batch'; SET 'parallelism.default' = '1'; SET 'table.dml-sync' = 'true'; SET 'sql-client.execution.result-mode' = 'tableau'; CREATE CATALOG paimon WITH ( 'type' = 'paimon', 'metastore' = 'filesystem', 'warehouse' = 'file:///tmp/paimon-commit-time-repro' ); USE CATALOG paimon; CREATE DATABASE tz_repro; USE tz_repro; CREATE TABLE orders ( id BIGINT, PRIMARY KEY (id) NOT ENFORCED ) WITH ('bucket' = '1'); INSERT INTO orders VALUES (1); ``` 2. Read `timeMillis` from `tz_repro.db/orders/snapshot/snapshot-1` under the warehouse. In my run, it was: ```text snapshot_id: 1 timeMillis: 1794897084761 UTC instant: 2026-11-17T06:31:24.761Z ``` Set the SQL session time zone to UTC and compare `commit_time` with the original epoch milliseconds. Replace the numeric literal with the `timeMillis` from your own snapshot: ```sql DESCRIBE `orders$snapshots`; SET 'table.local-time-zone' = 'UTC'; SELECT snapshot_id, commit_time, TO_TIMESTAMP_LTZ(1794897084761, 3) AS reference_instant FROM `orders$snapshots` WHERE snapshot_id = 1; ``` The schema reports `commit_time` as `TIMESTAMP(3) NOT NULL`. The query returns: ```text +-------------+-------------------------+-------------------------+ | snapshot_id | commit_time | reference_instant | +-------------+-------------------------+-------------------------+ | 1 | 2026-11-17 14:31:24.761 | 2026-11-17 06:31:24.761 | +-------------+-------------------------+-------------------------+ ``` 3. Set `table.local-time-zone` to `Asia/Shanghai` and repeat the query. Then start a new local SQL Client with `-Duser.timezone=UTC`, register the same catalog and select the same database, and repeat both session settings. Do not recreate the table or insert more data. The values for the same snapshot are: ```text Date: 2026-11-17 Reader JVM SQL session commit_time reference_instant -------------- -------------- ------------- ----------------- Asia/Shanghai UTC 14:31:24.761 06:31:24.761 Asia/Shanghai Asia/Shanghai 14:31:24.761 14:31:24.761 UTC UTC 06:31:24.761 06:31:24.761 UTC Asia/Shanghai 06:31:24.761 14:31:24.761 ``` 4. The same behavior occurs in Spark SQL. Configure the Paimon catalog to use the same warehouse and run in local mode. Start separate processes with driver JVM options `-Duser.timezone=Asia/Shanghai` and `-Duser.timezone=UTC`. In each process, execute: ```sql SET spark.sql.session.timeZone=UTC; SELECT snapshot_id, commit_time, typeof(commit_time) AS commit_time_type, timestamp_millis(1794897084761) AS reference_instant FROM paimon.tz_repro.`orders$snapshots` WHERE snapshot_id = 1; ``` With the reader JVM set to `Asia/Shanghai` and the SQL session set to `UTC`, Spark returns the following values (aligned for readability): ```text +-------------+-------------------------+------------------+-------------------------+ | snapshot_id | commit_time | commit_time_type | reference_instant | +-------------+-------------------------+------------------+-------------------------+ | 1 | 2026-11-17 14:31:24.761 | timestamp_ntz | 2026-11-17 06:31:24.761 | +-------------+-------------------------+------------------+-------------------------+ ``` Repeat with `SET spark.sql.session.timeZone=Asia/Shanghai;`, then repeat both session settings in the UTC JVM. Spark reports `commit_time_type = timestamp_ntz` in all four cases: ```text Date: 2026-11-17 Reader JVM SQL session commit_time commit_time_type reference_instant ------------- ------------- ------------ ---------------- ----------------- Asia/Shanghai UTC 14:31:24.761 timestamp_ntz 06:31:24.761 Asia/Shanghai Asia/Shanghai 14:31:24.761 timestamp_ntz 14:31:24.761 UTC UTC 06:31:24.761 timestamp_ntz 06:31:24.761 UTC Asia/Shanghai 06:31:24.761 timestamp_ntz 14:31:24.761 ``` ### What doesn't meet your expectations? The current `commit_time` column is a timestamp without a time zone, so changing the SQL session time zone alone is not expected to convert its value. The concern is that Paimon constructs this value using the reader JVM's default time zone, making the returned commit time depend on the environment that reads it. The desired interface contract is to expose a well-defined representation of the snapshot's commit instant that is independent of the reader JVM's default time zone. This could be an explicitly documented UTC value or a time-zone-aware timestamp interpreted using the SQL session time zone. Under the latter contract, a UTC session would display the UTC commit time. This is a request to clarify and improve the system table's contract, with compatibility considered for existing consumers. To demonstrate the impact on filtering, I ran the following Spark SQL against the same snapshot in two JVMs, keeping the SQL session time zone fixed at UTC: ```sql SET spark.sql.session.timeZone=UTC; SELECT COUNT(*) AS snapshots_before_cutoff FROM paimon.tz_repro.`orders$snapshots` WHERE snapshot_id = 1 AND commit_time < TIMESTAMP '2026-11-17 10:00:00'; ``` The snapshot's original `timeMillis` is `1794897084761`, corresponding to `2026-11-17T06:31:24.761Z`. The actual results are: ```text Reader JVM SQL session snapshots_before_cutoff -------------- -------------- ----------------------- UTC UTC 1 Asia/Shanghai UTC 0 ``` For a new table, choose a cutoff between the UTC and Asia/Shanghai representations of that snapshot to reproduce this difference. Only the reader JVM time zone changed; the query, session time zone and snapshot were identical, and the table files remained unchanged. The same time filter therefore selects different snapshots across environments. This makes queries used for commit monitoring or time-based statistics depend on a process-level setting that is separate from the SQL session configuration. The field should remain a timestamp type so that SQL filtering, ordering and time arithmetic remain available. ISO-8601 text such as `2026-11-17T06:31:24.761Z` is a separate formatting concern. ### Anything else? I reproduced all four JVM/session combinations in both Flink and Spark against the same filesystem table. The table files remained unchanged during the reads. I also checked the loaded connector classes and their bytecode; both builds use the conversion below. In [SnapshotsTable.java:94](https://github.com/apache/paimon/blob/d95fd18b48bd26ed394a113b255f369675c7e2d3/paimon-core/src/main/java/org/apache/paimon/table/system/SnapshotsTable.java#L94), the column is declared without time-zone semantics: ```java // The column contains a timestamp without a time zone. new DataField(5, "commit_time", new TimestampType(false, 3)), ``` In [SnapshotsTable.java:334–337](https://github.com/apache/paimon/blob/d95fd18b48bd26ed394a113b255f369675c7e2d3/paimon-core/src/main/java/org/apache/paimon/table/system/SnapshotsTable.java#L334-L337), the original epoch milliseconds are converted to the reader JVM's local date and time: ```java // The JVM default time zone is applied before the value reaches the engine. Timestamp.fromLocalDateTime( LocalDateTime.ofInstant( Instant.ofEpochMilli(snapshot.timeMillis()), ZoneId.systemDefault())) ``` `SchemasTable` 、 `ManifestsTable` and `FilesTable` also have the same problems. ### Are you willing to submit a PR? - [x] I'm willing to submit a PR! -- 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]
