On 1/13/23 16:03, Bryn Llewellyn wrote:
ken.tanzer@xxxxxxxxx <mailto:ken.tanzer@xxxxxxxxx> wrote:
I struggled to understand this whole murky area when I was writing the
“Date and time data types and functionality” section for the YugabyteDB
doc. (YugabyteDB uses the Postgres SQL processing code “as is” on top of
its own distributed storage layer. All the examples in my doc work
identically in vanilla PG.)
The implied question here is this: is the interval “1 day 2 hours” the
same as the interval “26 hours”? It might seem that the answer is
“yes”—as it surely must be. But, sorry to say, that the answer is
actually “no”. Confused? You will be. Most people are until they’ve
wrapped their head in a towel and puzzled it through for a few days.
Or read the docs:
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-OUTPUT
"Internally interval values are stored as months, days, and
microseconds. This is done because the number of days in a month varies,
and a day can have 23 or 25 hours if a daylight savings time adjustment
is involved."
This shows you what I mean:
set timezone = 'America/Los_Angeles';
with c as (
select '2023-03-11 20:00 America/Los_Angeles'::timestamptz as
original_appointment)
select
original_appointment::text as "original appointment",
(original_appointment + '1 day 2 hours'::interval)::text as
"postponed by '1_day 2 hours'",
(original_appointment + '26 hours'::interval)::text as "postponed by
'24_hours'"
from c;
This is the result:
original appointment | postponed by '1_day 2 hours' | postponed by
'24_hours'
------------------------+------------------------------+-------------------------
2023-03-11 20:00:00-08 | 2023-03-12 22:00:00-07 | 2023-03-12
23:00:00-07
Two different answers! The “trick” here is that the time of the original
appointment and the postponed times straddle the 2023 “spring forward”
moment (at least as it happens in the America/Los_Angeles timezone). And
the resolution of what at first might seem to be a bug come when you
realized that you must make a distinction between clock time and
calendar time.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
