Bryn Llewellyn <bryn@yugabyte.com> writes:
> There's more. We see that while the two "interval second” values '1 day 01:00:00' and '25 hours' test as equal, the
resultsof adding each to the same timestamptz value are different.
You're misunderstanding how it works. The whole point of these
operations is that "+ '1 day'" should give the same civil time
on the next day, even if a DST boundary intervened. Similarly,
"+ '1 month'" tries to give the same day number in the next
month, and "+ '1 year'" tries to give the same month/day in the
next year, despite varying month and year lengths. (Obviously,
there are edge cases where there is no such date, and you get some
nearby date instead.) Smaller units are taken literally though,
so you could add some large number of hours or seconds if you
don't want these behaviors.
> PLEASE STATE THE RULES THAT ALLOW THE BEHAVIOR OF THE BLOCK TO BE PREDICTED.
If you are looking for simple predictable rules, I am afraid
there is absolutely nothing about either the Gregorian calendar
or daylight-savings time that you will like.
> (I searched the doc but found nothing.)
There's a specific discussion of the DST-boundary issue on
this page:
https://www.postgresql.org/docs/current/functions-datetime.html
(search for the first mention of America/Denver). Not sure about
how well the months and years cases are documented.
regards, tom lane