search

MaterializeInc / materialize

The data warehouse for operational workloads.
https://materialize.com
Other
5.66k stars 457 forks source link

calculating with year intervals reports unexpected result on leap days #25642

Open def- opened 4 months ago

def- commented 4 months ago

What version of Materialize are you using?

main

What is the issue?

materialize=> select TIMESTAMP '2024-02-29 15:04:05.06' + interval '1 year';
        ?column?
------------------------
 2025-02-28 15:04:05.06
(1 row)

materialize=> select TIMESTAMP '2024-02-28 15:04:05.06' + interval '1 year';
        ?column?
------------------------
 2025-02-28 15:04:05.06
(1 row)

I would argue that TIMESTAMP '2024-02-29 15:04:05.06' + interval '1 year' should be March 1, 2025. Same for subtraction:

materialize=> select TIMESTAMP '2024-02-29 15:04:05.06' - interval '1 year';
        ?column?
------------------------
 2023-02-28 15:04:05.06
(1 row)
materialize=> select TIMESTAMP '2024-02-28 15:04:05.06' - interval '1 year';
        ?column?
------------------------
 2023-02-28 15:04:05.06
(1 row)

Postgres does the same. If this is intentional then we should at least document it in https://materialize.com/docs/sql/types/interval/

chaas commented 3 months ago

For date/time-related behavior we try to mirror postgres's behavior, so that's intentional. I'm looking in the postgres docs to see if we could link something to theirs about it, but it doesn't seem to be documented anywhere.