Re: Intervals and ISO 8601 duration

On 1/13/23 16:03, Bryn Llewellyn wrote:
>> ken(dot)tanzer(at)gmail(dot)com <mailto:ken(dot)tanzer(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com