This summer, with the help of AI, I found an inconsistency in the way Postgres handles timestamp vs. timestamptz comparisons under a DST spring-forward gap for the datetime_ops btree family [0]. Essentially there are scenarios where expression B > A and B < C, but also C = A, which can cause queries using a btree index (among other things) to return an incorrect result.
The assessment in the mailing list was that this was a bug, but there were no good ways to fix it.
> Backpatching a behavioral change like this seems awfully scary.
For the moment I'm just contemplating what we could potentially
change in master. So far I don't like any of the choices :-(
The problem really is inherent to DST itself, just as the month math in TFA is inherently wonky in any system. What's January 30th + 1 month? February 28th (or 29th, if a leap year)? March 1st? March 2nd?
UI time elements have to be presented in the user's TZ. In the DB one should store timestamptz in UTC for all things, and maybe also timestamptz in non-UTC TZs for user input (e.g., in a calendaring app).
tibbar · · focus · HN ↗
The assessment in the mailing list was that this was a bug, but there were no good ways to fix it.
> Backpatching a behavioral change like this seems awfully scary. For the moment I'm just contemplating what we could potentially change in master. So far I don't like any of the choices :-(
[0] <a href="https://www.postgresql.org/message-id/flat/CA%2BCOZaDmCuOds-MnMoDZwMVjxxby%3DW4W7_S0mxqS2ZNOYq0iJA%40mail.gmail.com" rel="nofollow">https://www.postgresql.org/message-id/flat/CA%2BCOZaDmCuOds-...
cryptonector · · focus · HN ↗
UI time elements have to be presented in the user's TZ. In the DB one should store timestamptz in UTC for all things, and maybe also timestamptz in non-UTC TZs for user input (e.g., in a calendaring app).