On 1/13/23 16:03, Bryn Llewellyn wrote:
>> ken.tanzer@gmail.com <mailto:ken.tanzer@gmail.com> 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@aklaver.com