Jubin Soni created FLINK-41499:
----------------------------------
Summary: FLOOR of a TIMESTAMP before 1970 to MONTH, QUARTER, YEAR
or WEEK returns a value after the input
Key: FLINK-41499
URL: https://issues.apache.org/jira/browse/FLINK-41499
Project: Flink
Issue Type: Bug
Components: Table SQL / Runtime
Affects Versions: 2.3.0, 2.5.0, 2.4.0
Reporter: Jubin Soni
h2. Bug / gap
DateTimeUtils.unixTimestampFloor (TIMESTAMP) and DateTimeUtils.timestampFloor
(TIMESTAMP_LTZ) compute the epoch day with (int) (millis / MILLIS_PER_DAY).
Java division rounds toward zero, so a TIMESTAMP before 1970 that is not at
midnight is mapped to the following day before the Julian floor is applied.
FLOOR(... TO MONTH | QUARTER | YEAR | WEEK | DECADE | CENTURY | MILLENNIUM)
then returns a value that can be later than the input. FLOOR ... TO DAY and
smaller units use a different path and are correct.
h2. Reproduction
{code}
SET 'table.local-time-zone' = 'UTC';
SELECT ts, FLOOR(ts TO MONTH), FLOOR(ts TO YEAR), FLOOR(ts TO QUARTER),
FLOOR(ts TO WEEK)
FROM (VALUES (CAST('1969-12-31 10:00:00' AS TIMESTAMP(3))),
(CAST('1969-11-30 23:59:59.999' AS TIMESTAMP(3))),
(CAST('1969-12-27 10:00:00' AS TIMESTAMP(3)))) AS T(ts);
1969-12-31T10:00 1970-01-01 1970-01-01 1970-01-01 1969-12-28
1969-11-30T23:59:59.999 1969-12-01 1969-01-01 1969-10-01 1969-11-30
1969-12-27T10:00 1969-12-01 1969-01-01 1969-10-01 1969-12-28
{code}
Expected:
{code}
1969-12-31T10:00 1969-12-01 1969-01-01 1969-10-01 1969-12-28
1969-11-30T23:59:59.999 1969-11-01 1969-01-01 1969-10-01 1969-11-30
1969-12-27T10:00 1969-12-01 1969-01-01 1969-10-01 1969-12-21
{code}
FLOOR(CAST(ts AS TIMESTAMP_LTZ(3)) TO MONTH) is wrong the same way
(1970-01-01T00:00:00Z for 1969-12-31 10:00). Reproduced on the 2.1.2 release,
and with DateTimeUtils from current master.
h2. Why it matters
Silent wrong result: FLOOR returns a value greater than its argument for
historical timestamps (for example birth dates or archived events), which
breaks monthly or yearly bucketing and GROUP BY FLOOR(...) aggregations without
any error.
h2. Related work
FLINK-41280 fixed the same round-toward-zero problem for CAST of a TIMESTAMP
before 1970 to DATE, TIME and a lower precision (backported to 2.4, 2.3.1,
2.2.2, 1.20.6). FLOOR was not covered. Searched JIRA for FLOOR/CEIL + 1970,
"before 1970", "pre-epoch", unixTimestampFloor, and GitHub PRs for
unixTimestampFloor/julianDateFloor/DateTimeUtils on 2026-11-14: no existing
ticket or PR. CEIL is not changed here: for pre-1970 values it currently
returns correct results, and its day-1 behaviour is tracked by the CALCITE-3199
TODO in FunctionGenerator.
h2. Proposed fix
Use Math.floorDiv(millis, MILLIS_PER_DAY) for the day number in
DateTimeUtils.unixTimestampFloor and DateTimeUtils.timestampFloor (non-DAY
units), as FLINK-41280 did for CAST. Add pre-1970 FLOOR cases on a column value
(TIMESTAMP and TIMESTAMP_LTZ; MONTH, QUARTER, YEAR, WEEK) to
TimeFunctionsITCase#floorTestCases.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
