[ 
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)

Reply via email to