pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PALAYRET Jacques <jacques.palayret@meteo.fr>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-general@postgresql.org
Subject: Re: Interval in hours but not in days  Leap second not taken into account
Date: Mon, 27 Feb 2023 08:50:02 +0000 (GMT)
Message-ID: <1309241996.22910770.1677487802466.JavaMail.zimbra@meteo.fr> (raw)
In-Reply-To: <96ad392589f3f147b0b5dd240f4903cd58eb5bfb.camel@cybertec.at>
References: <1200461876.22810256.1677482775363.JavaMail.zimbra@meteo.fr>
	<96ad392589f3f147b0b5dd240f4903cd58eb5bfb.camel@cybertec.at>


Does PostgreSQL take into account daylight saving time in its calendar?

For the last summer hour of the spring (Daylight Saving Time), on Sunday March 27, 2022:
SELECT to_timestamp('20220329 00:00:00','yyyymmdd hh24:mi:ss') - to_timestamp('20220320 00:00:00','yyyymmdd hh24:mi:ss') intervalle ;
 intervalle
------------
 9 days


Regards
----- Mail original -----
De: "Laurenz Albe" <laurenz.albe@cybertec.at>
À: "PALAYRET Jacques" <jacques.palayret@meteo.fr>, pgsql-general@postgresql.org
Envoyé: Lundi 27 Février 2023 09:23:37
Objet: Re: Interval in hours but not in days  Leap second not taken into account

On Mon, 2023-02-27 at 07:26 +0000, PALAYRET Jacques wrote:
> # An interval in " years months ... seconds " given in seconds by EXTRACT(EPOCH ...) transtyped into INTERVAL :
> SELECT (EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes 12 seconds'::interval) ) || ' seconds')::interval ;
>   interval
> -------------
>  27772:11:12
> 
> # The same interval in seconds formated with TO_CHAR() :
> SELECT TO_CHAR((EXTRACT(EPOCH FROM ('3 years 2 months 1 day 10 hours 11 minutes 12 seconds'::interval) ) || ' seconds')::interval, ' yyyy mm dd_ hh24-mi-ss ') ;
>           to_char
> ---------------------------
>   0000 00 00_ 27754-11-12
> 
> => The result is given in hours ... (not in days ...).
> 
> It is logical that there are neither years nor months because they are not constant
> (leap year or not; a month can contain 31 30 ... days).
> I thought that days were eliminated because of the leap second (extra seconds
> inserted in the UTC time scale); obviously, this is not the case.
> 
> # PostgreSQL does not take into account the additional second (leap second) in some calendar days ; eg. 2016, 31 dec. :
> SELECT to_timestamp('20170102 10:11:12','yyyymmdd hh24:mi:ss') - to_timestamp('20161230 00:00:00','yyyymmdd hh24:mi:ss') intervalle ;
>    intervalle
> -----------------
>  3 days 10:11:12
> --> With postgreSQL, a calendar day is always 86,400 seconds long.
> 
> So, is there a reason for this (interval in hours ...) ?

The best explanation I have is "daylight savings time".
One day is not always 24 hours long.
If you keep the interval in hours, the result is always correct (if you
ignore leap seconds, which PostgreSQL doesn't account for).

Yours,
Laurenz Albe





view thread (5+ messages)  latest in thread

Message-ID: <1309241996.22910770.1677487802466.JavaMail.zimbra@meteo.fr>
Permalink:  ../1309241996.22910770.1677487802466.JavaMail.zimbra@meteo.fr/
Also on:    postgresql.org/message-id/1309241996.22910770.1677487802466.JavaMail.zimbra@meteo.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: jacques.palayret@meteo.fr, laurenz.albe@cybertec.at
  Subject: Re: Interval in hours but not in days  Leap second not taken into account
  In-Reply-To: <1309241996.22910770.1677487802466.JavaMail.zimbra@meteo.fr>
  References: <1200461876.22810256.1677482775363.JavaMail.zimbra@meteo.fr> <96ad392589f3f147b0b5dd240f4903cd58eb5bfb.camel@cybertec.at> <1309241996.22910770.1677487802466.JavaMail.zimbra@meteo.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox