[
https://issues.apache.org/jira/browse/FLINK-41499?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18144966#comment-18144966
]
Jubin Soni commented on FLINK-41499:
------------------------------------
I'm working on this.
> 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.4.0, 2.5.0
> Reporter: Jubin Soni
> Priority: Major
>
> 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)