> Adding a month with + INTERVAL '1 months' is timezone-dependent. […]
Adding months isn’t well-defined anyway, even when using date, for days of month > 28. I think it’s a mistake that systems generically allow such a computation (as opposed to application code implementing domain-specific business rules).
Looking... I can't find it with Google, but <a href="https://www.sqlite.org/lang_datefunc.html" rel="nofollow">https://www.sqlite.org/lang_datefunc.html mentions it:
| Because the length of a month or year changes from one month or year to the next, ambiguities can arise when shifting a date by months and/or years. For example, what is the date one year after 2024-02-29? Is it 2025-02-28 or 2025-03-01? Or what is the date that is two months after 2023-12-31? Is it 2024-02-29 or 2024-03-02? There is no consensus on how to resolve this ambiguity, so the "ceiling" and "floor" modifiers (14 and 15) are available to let the programmer decide. If the next modifier after a time shift is "ceiling", then any ambiguity in the date is resolved by choosing the later date. The "floor" modifier resolves ambiguities by resolving to the last day of the previous month. The default behavior is "ceiling".
layer8 · · focus · HN ↗
Adding months isn’t well-defined anyway, even when using date, for days of month > 28. I think it’s a mistake that systems generically allow such a computation (as opposed to application code implementing domain-specific business rules).
cryptonector · · focus · HN ↗
layer8 · · focus · HN ↗
cryptonector · · focus · HN ↗
| Because the length of a month or year changes from one month or year to the next, ambiguities can arise when shifting a date by months and/or years. For example, what is the date one year after 2024-02-29? Is it 2025-02-28 or 2025-03-01? Or what is the date that is two months after 2023-12-31? Is it 2024-02-29 or 2024-03-02? There is no consensus on how to resolve this ambiguity, so the "ceiling" and "floor" modifiers (14 and 15) are available to let the programmer decide. If the next modifier after a time shift is "ceiling", then any ambiguity in the date is resolved by choosing the later date. The "floor" modifier resolves ambiguities by resolving to the last day of the previous month. The default behavior is "ceiling".
hulitu · · focus · HN ↗
2025-03-01
> Or what is the date that is two months after 2023-12-31? Is it 2024-02-29 or 2024-03-02?
2024-02-29
But better use units of fixed size, if you do not want trouble.