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]

Reply via email to