[
https://issues.apache.org/jira/browse/FLINK-41307?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18139822#comment-18139822
]
Shawn commented on FLINK-41307:
-------------------------------
[~dianfu] I'd like to fix this by passing the LocalDateTime fields to Python
without going through an instant. Could you assign it to me?
> PyFlink TableResult.collect() changes TIMESTAMP values in a DST gap or before
> 1902 (converted through the JVM and Python local time zones)
> ------------------------------------------------------------------------------------------------------------------------------------------
>
> Key: FLINK-41307
> URL: https://issues.apache.org/jira/browse/FLINK-41307
> Project: Flink
> Issue Type: Bug
> Components: API / Python
> Affects Versions: 2.0.1, 2.3.0, 2.2.1
> Reporter: Yaoxuan Wu
> Priority: Major
> Labels: correctness
>
> {code:python}
> from pyflink.table import EnvironmentSettings, TableEnvironment
> t_env = TableEnvironment.create(EnvironmentSettings.in_batch_mode())
> vals = ['2020-03-08 02:30:00', '1900-01-01 00:00:00', '1800-01-01 00:00:00',
> '2020-07-01 12:00:00']
> q = " UNION ALL ".join(f"SELECT {i} AS i, TIMESTAMP '{v}' AS ts" for i, v in
> enumerate(vals))
> with t_env.execute_sql(q).collect() as it:
> for r in sorted(it, key=lambda r: r[0]):
> print(vals[r[0]], '->', r[1])
> # expected: each value unchanged
> # actual (TZ=America/Los_Angeles):
> # 2020-03-08 02:30:00 -> 2020-03-08 03:30:00
> # 1900-01-01 00:00:00 -> 1899-12-31 23:53:00
> # 1800-01-01 00:00:00 -> 1800-01-01 00:00:02
> # 2020-07-01 12:00:00 -> 2020-07-01 12:00:00
> {code}
> {{TableResult.collect()}} changes TIMESTAMP (without time zone) values that
> are not a plain instant in the client's default time zone: a time in a DST
> gap moves by an hour, and times before 1901-12-13 move by the difference
> between two LMT offsets. {{TableResult.print()}} shows the original values;
> with {{TZ=UTC}} nothing changes.
> Cause: the value goes through two different local-zone conversions.
> # {{PythonBridgeUtils.getPickledBytesFromJavaObject}}, TimestampType:
> {{pickler.dumps(Timestamp.valueOf((LocalDateTime) obj))}}.
> {{Timestamp.valueOf}} interprets the wall clock in the JVM default zone
> (02:30 in the gap becomes 03:30 PDT).
> # The shaded Pyrolite {{Pickler}} turns the {{java.util.Date}} into a
> {{GregorianCalendar}} and pickles {{pytz.timezone(<zone
> id>).localize(datetime(<calendar fields>))}}. pytz only has the 32-bit
> transitions (the first one for America/Los_Angeles is 1901-12-13 20:45:52
> UTC), so earlier times are localized as LMT, which pytz rounds to whole
> minutes (-7:53).
> # {{pickled_bytes_to_python_converter}} (pyflink/table/utils.py) does
> {{field_type.from_sql_type(int(data.timestamp() * 10**6))}}, and
> {{TimestampType.from_sql_type}} is {{datetime.fromtimestamp(...)}}, i.e. back
> to a naive time in the Python local zone, which uses the full tzdata (PST
> -8:00 in 1900, LMT -7:52:58 in 1800).
> A TIMESTAMP has no zone, so the LocalDateTime fields could be passed without
> going through an instant.
> Reproduces on 2.0.1, 2.2.1, 2.3.0 and master.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
