Re: Intervals and ISO 8601 duration

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