sepuri sai krishna created FLINK-41596:
------------------------------------------

             Summary: TIMESTAMPDIFF with MONTH, QUARTER or YEAR is off by one 
when the second argument is earlier
                 Key: FLINK-41596
                 URL: https://issues.apache.org/jira/browse/FLINK-41596
             Project: Flink
          Issue Type: Bug
          Components: Table SQL / Runtime
    Affects Versions: 2.3.0, 2.4.0
            Reporter: sepuri sai krishna


When the second argument of TIMESTAMPDIFF is earlier than the first, MONTH, 
QUARTER and YEAR results can be one too far from zero. Swapping the arguments 
changes more than the sign:

{code:sql}
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '2021-01-04 00:00:00', TIMESTAMP 
'2021-02-04 00:00:01'); -- 1
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '2021-02-04 00:00:01', TIMESTAMP 
'2021-01-04 00:00:00'); -- -2
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '1970-01-01 00:00:01', TIMESTAMP 
'1970-01-01 23:59:59'); -- 0
SELECT TIMESTAMPDIFF(MONTH, TIMESTAMP '1970-01-01 23:59:59', TIMESTAMP 
'1970-01-01 00:00:01'); -- -1
{code}

The second and fourth should be -1 and 0, which is what MySQL returns.

The same happens with TIMESTAMP_LTZ, with a DATE and a TIMESTAMP, and with 
timestampDiff in the Table API.

Checked on 2.3.0 and master, JDK 17.




--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

Reply via email to