[Compatibility] Timestamp parsing and timezone conversion differ from Flink, Spark and Presto

[yangzhg]

Reference Engine

Apache Flink, Apache Spark, PrestoDB.

Affected Function / Operator

• Flink: TO_TIMESTAMP(string) and TO_TIMESTAMP(string, format).
• Spark: formatted to_timestamp / Bolt get_timestamp, and unix_timestamp, under CORRECTED and LEGACY.
• Presto: parse_datetime. date_parse uses a separate MySQL-pattern entry and is included below where the expected rules differ.
• Shared code: DateTimeFormatter, timestamp/calendar conversion, and timezone-offset lookup.

This issue collects the timestamp compatibility gaps found while reviewing these paths. The tables describe the existing implementation at the baseline below.

Reproduction (Query & Data)

No tables or external data are needed. Each input/pattern pair below can be used in these expressions:

-- Flink
SELECT TO_TIMESTAMP('1970/01/01 00:00:00.123456789',
                    'yyyy/MM/dd HH:mm:ss.SSSSSSSSS');
SELECT TO_TIMESTAMP('2023/02/29', 'yyyy/MM/dd');
SELECT TO_TIMESTAMP('2023-02-29', 'yyyy/MM/dd');

-- Spark
SET spark.sql.session.timeZone=UTC;
SET spark.sql.ansi.enabled=false;
SET spark.sql.legacy.timeParserPolicy=CORRECTED;
SELECT to_timestamp('70/01/01', 'yy/MM/dd');
SELECT to_timestamp('1970/01/01 23 PM', 'yyyy/MM/dd HH a');

-- Presto, with session timezone UTC
SELECT parse_datetime('1970 marrch 01', 'yyyy MMM dd');
SELECT parse_datetime('2024-01-02tail', 'yyyy-MM-dd');
SELECT parse_datetime('2024-01-02 003', 'yyyy-MM-dd D');

Use LEGACY and the specified session timezone for the Spark legacy/timezone cases. The Flink JVM default timezone is UTC except where stated otherwise.

Result Comparison

Expected Behavior (Reference Engine Result)

The expected column in each table is from the corresponding Java parser entry. Fractional precision is compared on the returned timestamp value, rather than relying on client display formatting. A date without a time means midnight. Spark timestamps are shown in UTC unless a timezone is explicitly stated. Reject means a parser failure; ordinary Spark parse failures become SQL NULL with ANSI disabled. Invalid patterns and arithmetic overflow have their own exception behavior.

Actual Behavior (Bolt Result)

1. Shared format parsing: Flink and Spark

C and L mean Spark CORRECTED and LEGACY.

ID	Case: input / pattern	Reference result	Bolt result	Affected entry
S1	01-02 / MM-dd; also month-only and day-only	Default year 1970	Default year 2000	Flink, Spark C/L
S2	70/01/01 / yy/MM/dd	2070-01-01	1970-01-01	Flink, Spark C
S3	1970/1/1 / yyyy/MM/dd; 70/01/01 / yyyy/MM/dd	Reject incorrect field widths	Accepts both	Flink, Spark C
S4	1400-365 / yyyy-DD	Reject three digits for DD	1400-12-31	Flink, Spark C
S5	+1970/01/01 and 11970/01/01 / yyyy/MM/dd	Reject both; the valid expanded form is +11970/01/01	Accepts both invalid forms	Flink, Spark C
S6	-0001/01/01 / yyyy/MM/dd	Spark C accepts year -1; Flink rejects year-of-era without an era	Spark returns NULL; Flink accepts year -1	Spark C, Flink
S7	BC 0001/01/01 / G yyyy/MM/dd	Proleptic year 0	AD year 1	Flink, Spark C/L
S8	0000/01/01 / yyyy/MM/dd; AD 0000/01/01 / G yyyy/MM/dd	Flink and Spark L reject both. Spark C accepts the first, rejects the second	Accepts both in these entries	Flink, Spark C/L, as specified
S9	Conflicting repeated year/month/hour: 1970/2000-01-01 / yyyy/yyyy-MM-dd, 1970-01/02-01 / yyyy-MM/MM-dd, 1970-01-01 01/02 / yyyy-MM-dd HH/HH	Reject	Last value wins	Flink, Spark C
S10	1970/01/01 23 PM / yyyy/MM/dd HH a	Same day 23:00	Next day 11:00	Flink, Spark C/L
S11	23 AM or 11 PM with HH a; 23 10 PM with HH hh a, after a full date	Reject inconsistent clock fields	Accepts and produces a timestamp	Flink, Spark C/L
S12	Fractions 100000001 100000002 with SSSSSSSSS SSSSSSSSS after a full timestamp	Reject conflicting nanoseconds, including in Spark before truncating the result to microseconds	Both become .100000 and the conflict is lost	Flink, Spark C
S13	2024-01-01-060 / yyyy-MM-dd-DDD, and 2024-060-01-01 / yyyy-DDD-MM-dd	Reject contradictory calendar dates	First form becomes Feb 29; second becomes Jan 1	Flink, Spark C/L
S14	2024-060-01 / yyyy-DDD-MM; analogous DDD plus day-of-month cases	Cross-check the remaining date field; reject the conflict	Accepts it	Flink, Spark C/L
S15	1970 Jan 01 / yyyy MMMM dd, or 1970 January 01 / yyyy MMM dd	Reject incorrect text width	Accepts both	Flink, Spark C
S16	1970 marrch 01 or 1970 MARRCH 01 / yyyy MMM dd	Reject the misspelling	March 1	Flink, Spark C/L; also Presto
S17	1970 jan 01 / yyyy MMM dd	Flink rejects lowercase text	January 1	Flink
S18	1970 jAn 01 / yyyy MMM dd, or mixed-case pM with hh a	Spark C/L accept case-insensitive text	Rejects mixed case	Spark C/L
S19	1970-01-01t00:00:00 / yyyy-MM-dd'T'HH:mm:ss	Spark C accepts the lowercase literal	NULL	Spark C
S20	E in a timestamp parsing pattern, e.g. E-yyyy-MM-dd	Spark C rejects the pattern; E remains valid for formatting and LEGACY parsing	Can parse a value or return NULL depending on field order, instead of rejecting the pattern	Spark C

2. Flink-specific parsing and fallback

Flink's toTimestampData first uses java.time.DateTimeFormatter, reads the resolved fields, fills missing fields, and only on DateTimeParseException tries java.sql.Timestamp.valueOf / java.sql.Date.valueOf. The fallback has different calendar and normalization rules. Reference implementation.

ID	Case	Flink result	Bolt result
F1	.123456789 / nine S characters, including formatted input and the one-argument entry	Preserves all nine fractional digits	.123456000 in a Spark-compatible build; the shared parser uses milliseconds in a non-Spark build
F2	1969/12/31 23:59:59.999999999 / yyyy/MM/dd HH:mm:ss.SSSSSSSSS	Timestamp(-1, 999999999)	Timestamp(-1, 999999000) in the tested build
F3	2023/02/29, 2023/02/30, 2023/04/31 / yyyy/MM/dd	SMART resolution gives Feb 28, Feb 28, Apr 30	NULL
F4	1970/01/01 24:00:00 / yyyy/MM/dd HH:mm:ss	Next day at midnight	NULL
F5	24:00 / HH:mm, or 1970-01 24 / yyyy-MM HH	1970-01-01 00:00:00; the excess day is not carried into fields filled later	NULL
F6	04 / hh; PM / a; 060 / DDD	1970-01-01 00:00:00 in all three cases	04:00, 12:00, and 2000-02-29 respectively
F7	02-29 / MM-dd	Completing the missing year with 1970 raises DateTimeException	2000-02-29
F8	.1 / nine S characters after 1970/01/01 00:00:00	Format-width failure, then fallback failure: NULL	Accepts .100000000
F9	2024-01-01-Mon / yyyy-MM-dd-E; Tue-2024-01-01 / E-yyyy-MM-dd	Jan 1 for the matching weekday; NULL for the conflict	NULL for the matching weekday; Jan 1 for the conflict
F10	1970/01/01 / uuuu/MM/dd	1970-01-01	NULL
F11	2023-02-29 / yyyy/MM/dd	Primary parse fails; fallback normalizes to 2023-03-01	NULL. With the matching pattern yyyy-MM-dd, Flink instead returns Feb 28; Bolt returns NULL there too
F12	2020-01-01 24:00:01 / yyyy/MM/dd	Fallback normalizes to 2020-01-02 00:00:01	NULL
F13	1582-10-10 / yyyy/MM/dd	Fallback's hybrid calendar gives 1582-10-20	1582-10-10
F14	１９７０-01-01 / yyyy/MM/dd	Fallback accepts the BMP Unicode digits and returns 1970-01-01	NULL
F15	1970-1-1 0:0:0.000000001 / yyyy/MM/dd	Fallback preserves one nanosecond	Zero nanoseconds
F16	0000-02-29 / yyyy/MM/dd	The Java entry raises DateTimeException while converting the normalized date	Returns a timestamp; the failure behavior differs
F17	2021-03-14 02:30:00.123456789 / yyyy/MM/dd, JVM default zone America/New_York	Fallback passes through java.sql.Timestamp and returns local 03:30:00.123456789	Local 02:30:00.123456000; JVM default-zone context is absent

The JVM timezone dependency in F17 belongs to the fallback. A successful primary parse of the same local timestamp does not apply that normalization. Historical fallback cases also depend on Java's calendar and TimeZone rules; the session timezone alone is insufficient context. Unicode support needs to follow the Java entry's UTF-16 digit rules rather than accepting every Unicode numeric character.

3. Spark LEGACY and timezone conversion

ID	Case	Spark result	Bolt result
L1	70/01/01 / y/MM/dd, LEGACY	1970-01-01: exactly two input digits use the legacy window even with one y	Year 70
L2	Two-digit years at the formatter's century-window boundary	Uses the formatter's complete saved start instant and start year	Fixed 1970–2069 interpretation; no formatter pivot context
L3	1970/01/01 00:00:00.123456789 / yyyy/MM/dd HH:mm:ss.S, LEGACY	Rejects out-of-range milliseconds with SQL's non-lenient SimpleDateFormat	Accepts .123
L4	+1970/01/01 / yyyy/MM/dd, LEGACY	Reject	Accepts it
L5	BC / G, LEGACY	Default year-of-era 1970 in BC, epoch seconds -124302816000	AD 1970
L6	1582-356 / yyyy-DDD; 1582-52-1 / yyyy-ww-u, LEGACY	Invalid hybrid-calendar dates produce parse failure / SQL NULL	Throws during native date construction
L7	1991-04-14 02:30:00 / yyyy-MM-dd HH:mm:ss, CORRECTED, Asia/Shanghai	Gap is shifted forward; epoch seconds 671567400	NULL
L8	1900-12-31 23:59:59 / yyyy-MM-dd HH:mm:ss, LEGACY, Asia/Shanghai	Epoch seconds -2177481601	-2177481944, a 343-second difference
L9	1949-05-01 00 / yyyy-MM-dd HH, LEGACY, Asia/Shanghai	Rejects the explicitly supplied hour in the gap	Accepts and shifts it to 01:00
L10	2021-10-03 02:00 / yyyy-MM-dd HH:mm, LEGACY, Australia/Lord_Howe	Rejects the explicit minute changed by the half-hour gap	Accepts and shifts it to 02:30
L11	2011-12-30 / yyyy-MM-dd, LEGACY, Pacific/Apia	Rejects the missing calendar date	Accepts and moves it across the whole-day gap
L12	0001-01-01 00:00:00 +0000 / yyyy-MM-dd HH:mm:ss Z, LEGACY, session Asia/Shanghai	Epoch seconds -62135597143 after session-zone calendar rebasing	-62135596800
L13	1582-10-15 00:00:00 +0000 with the same pattern, LEGACY, session America/New_York	Epoch seconds -12220157038	-12219292800
L14	1899-12-31 23:59:59 +0000 with the same pattern, LEGACY, session Europe/Dublin	Epoch seconds -2208987280	-2208988801
L15	1970/01/01 00:00:00 +01:00:01 / yyyy/MM/dd HH:mm:ss XXXXX, LEGACY	Rejects the invalid pattern; SimpleDateFormat supports at most three X characters	Accepts it as 1969-12-31 23:00:00 UTC, ignoring the seconds

L2 is not reproducible by choosing a new fixed pivot year. For a deterministic SimpleDateFormat start of 1946-09-21 08:13:14.500 UTC:

46-09-21 08:13:14.499 -> 2046-09-21 08:13:14.499
46-09-21 08:13:14.500 -> 1946-09-21 08:13:14.500
46-09-21 08:13:14.501 -> 1946-09-21 08:13:14.501

Bolt's fixed window maps all three to 2046. The start year is captured in the formatter's initial JVM timezone; changing the session timezone afterward does not recompute it. At the same pivot instant, deriving the year from the session timezone can select the wrong century. pivotMillis, pivotYear, and formatter lifetime all matter. SimpleDateFormat year-window rules.

L9–L11 depend on which fields were explicitly supplied. The Shanghai date-only form succeeds, as does the Lord Howe hour-only form (02 / HH), because the omitted fields can normalize without changing an explicit field. Rejecting every gap would also disagree with Spark.

L12–L14 cover the second conversion from the legacy Java timestamp to Spark's proleptic timestamp in the session zone, including explicit-offset inputs. Raw Java TimeZone offsets and native historical LMT offsets are not interchangeable. Cases around 1500/1582, the 1900 boundary, BC years, and zones such as Dublin and Apia need the same treatment.

The reference is Spark's SQL SIMPLE_DATE_FORMAT path with lenient=false, including its call to fromJavaTimestamp(zoneId.getId, ...). Spark 3.5.7 formatter.

4. Presto parse_datetime

The Java entry calls Joda DateTimeFormat.forPattern(...).withChronology(...).withOffsetParsed().withLocale(...), followed by parseDateTime. The following comparisons use that exact parsing setup in UTC with Joda 2.14.0, the version pinned by Presto 0.298.1. Entry point, dependency version.

ID	Case: input / pattern	Presto/Joda result	Bolt parse_datetime result
P1	2024-01-02tail / yyyy-MM-dd	Rejects trailing text	2024-01-02
P2	1970 marrch 01 / yyyy MMM dd	Reject	1970-03-01
P3	2024-01-02 003 / yyyy-MM-dd D	2024-01-02	2024-01-03
P4	2024-01-01-060 / yyyy-MM-dd-DDD, and 2024-060-01-01 / yyyy-DDD-MM-dd	2024-02-01 for both, following Joda field precedence	Feb 29 for the first; Jan 1 for the second
P5	Tue-2024-01-01 / E-yyyy-MM-dd	2024-01-02	2024-01-01
P6	Mon / E	2000-12-25	2000-01-03
P7	1970/01/01 23 PM / yyyy/MM/dd HH a	Same day 23:00	Next day 11:00
P8	1970/01/01 11 PM / yyyy/MM/dd HH a	Same day 11:00; Joda's HH determines the result here	Same day 23:00
P9	1970-01-01t00:00:00 / yyyy-MM-dd'T'HH:mm:ss	Accepts the case-insensitive literal	Reject

P7/P8 depend on SPARK_COMPATIBLE=ON: the shared scanner marks a as a half-day hour even through the Presto registration. These two rows need separate coverage in a non-Spark build.

These field-precedence results must be compared against Joda. Applying Flink/Spark conflict rejection to every Presto input would introduce new differences.

5. Offset range and precision

ID	Case	Reference result	Bolt result
Z1	1970/01/01 00:00:00 +18:00 / yyyy/MM/dd HH:mm:ss XXX	Spark C: 1969-12-31 06:00:00 UTC; Flink: local 1970-01-01 00:00:00	NULL in both entries
Z2	1970/01/01 00:00:00 +01:00:01 / yyyy/MM/dd HH:mm:ss XXXXX	Spark C: 1969-12-31 22:59:59 UTC; Flink: local 1970-01-01 00:00:00	NULL in both entries

The shared offset-ID lookup is limited to minute offsets in ±14 hours. Ordinary XXX offsets such as +05:30 do parse; the gaps are the supported range and second-level offsets. Flink TO_TIMESTAMP returns local fields after a successful format parse, whereas Spark applies the parsed offset to obtain an instant.

6. Engine differences that are expected

Rule	Flink TO_TIMESTAMP	Spark	Presto
Fraction precision	Nanoseconds	Microseconds for timestamps; excess parsed fraction is truncated	Milliseconds for these timestamp functions
Missing year in MM-dd	1970	1970	Joda path: 2000
Two-digit year	yy: 2000–2099	CORRECTED: 2000–2099; LEGACY: formatter-dependent window	MySQL date_parse has its own 1970–2069 window
Invalid month-end date	SMART primary parsing may clamp to month end	CORRECTED rejects; SQL LEGACY is also non-lenient	Joda rejects
.1 against SSS	Primary parse fails; fallback depends on the complete input	CORRECTED: 100 ms; LEGACY: 1 ms	Joda: 100 ms
Month text	Case-sensitive, width-sensitive	CORRECTED is case-insensitive and width-sensitive; LEGACY has different width rules	Joda accepts short/full month names for these patterns; arbitrary mixed case is not accepted
Conflicting duplicate fields	Java field conflict checks	CORRECTED checks conflicts; LEGACY permits some repeated fields to overwrite, but validates other combinations	Joda's saved-field ordering applies
Trailing text	Primary parse requires complete consumption; then fallback may run	CORRECTED requires complete consumption; LEGACY can accept a parsed prefix	parse_datetime requires complete consumption
Calendar	Primary parse is proleptic Gregorian; fallback uses legacy Java conversion	CORRECTED is proleptic; LEGACY includes hybrid-calendar parsing and session-zone rebasing	Joda chronology for parse_datetime; MySQL-pattern construction for date_parse

Spark's documented precision and pattern rules are in Datetime Patterns. Presto documents the separate Joda and MySQL parsing functions.

Bolt Version / Commit ID

Public source baseline: bytedance/bolt main at 7fb4079e3da7c74c5b24de794d2a9263f918f9d3.

Native reproductions use the saved 1a69deb070d3df27309212e84bfa127180b1f6e2 build with SPARK_COMPATIBLE=ON. The formatter implementation/builder, Flink ToTimestampFunction.h, and Spark/Presto DateTimeFunctions.h were compared with the public baseline and are identical. The fractional result for the non-Spark build in F1 follows the compile-time precision branch; that build was not rerun for this report.

Reference Engine Version

• Flink 1.11 SqlDateTimeUtils.toTimestampData; public source pinned above. Executed against a 1.11-based runtime with the same method implementation.
• Spark 3.5.7 runtime jars, invoking the SQL timestamp formatter with LegacyDateFormats.SIMPLE_DATE_FORMAT and the stated parser policy.
• PrestoDB 0.298.1 source and Joda-Time 2.14.0, invoking the parser used by parse_datetime. A full Presto server was not used.
• Java 8u362, English/US locale.

Important Configurations (Critical)

• JVM default timezone: UTC, except the explicit Flink fallback and legacy-window examples.
• Session timezone: UTC unless a row specifies another zone.
• Spark SQL: spark.sql.ansi.enabled=false; spark.sql.legacy.timeParserPolicy set explicitly to CORRECTED or LEGACY.
• Bolt query settings: matching parser policy, session_timezone, and adjust_timestamp_to_timezone=true for Spark/Presto tests.
• Historical timezone results depend on both JVM tzdb and native tzdb. Java's raw offset, the legacy formatter pivot, and the session zone represent different pieces of context.

Additional context

The existing timezone report #965 covers post-2037 DST rules and legacy historical offsets in formatting. The CAST gap report #1021 covers a different entry; the Shanghai parsing case L7 still differs at this baseline.

Further coverage is needed for EXCEPTION policy and ANSI errors, constant versus per-row formats, invalid pattern versus invalid input, NULL propagation, locale-sensitive text/week rules, and every MySQL date_parse counterpart. Those are coverage boundaries, not additional confirmed mismatches in this report.

Boundary cases to retain in the regression set include fraction precisions 0–9 and negative epochs; consistent as well as conflicting duplicate fields; weekday checks after SMART date/24:00 resolution; JVM versus session timezone differences; century-window millisecond boundaries; overflow after applying an offset; and DATE/TIMESTAMP overloads that do not parse the format argument. The tested Spark unix_timestamp(DATE '2018-11-04') in America/Sao_Paulo already matches at 1541300400.