From: Alban Hertroys <haramrae@gmail.com>
To: Ken Tanzer <ken.tanzer@gmail.com>
Cc: Alejandro Baeza Rangel <jlabaezarangel@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Duda sobre como imprimir un campo INTERVAL
Date: Sat, 19 Nov 2022 11:39:43 +0100
Message-ID: <7DD6FF46-1F92-4E6B-A288-45A26021EE75@gmail.com> (raw)
In-Reply-To: <CAD3a31Xo1zGKRDNNQizU8123WvcjN_zheCHU9V+c_gR5GM1ayw@mail.gmail.com>
References: <CAJKTcg9jLfH+-v8TS2gV=vZzqjLZ5cYyMVUtGxwyRitER5htMg@mail.gmail.com>
<CAD3a31Xo1zGKRDNNQizU8123WvcjN_zheCHU9V+c_gR5GM1ayw@mail.gmail.com>
> On 19 Nov 2022, at 4:58, Ken Tanzer <ken.tanzer@gmail.com> wrote:
>
> On Thu, Nov 17, 2022 at 2:30 PM Alejandro Baeza Rangel <jlabaezarangel@gmail.com> wrote:
(…)
> don't fully understand it. But what really confuses me is the example below. How can these two intervals be equal and still yield different output in the to_char function? And as a practical matter, and for the OPs question, how can you convert from one to the other of these "equal" values?
>
> WITH inters AS (
> SELECT
> '1 day 2 hours'::interval AS i1,
> '26 hours'::interval AS i2
> )
> SELECT
> *,
> to_char(i1,'HH24:MM:SS') AS i1_char,
> to_char(i2,'HH24:MM:SS') AS i2_char,
> i1=i2 AS "Equal?"
> FROM inters;
>
> i1 | i2 | i1_char | i2_char | Equal?
> ----------------+----------+----------+----------+--------
> 1 day 02:00:00 | 26:00:00 | 02:00:00 | 26:00:00 | t
>
> Cheers,
> Ken
Those intervals are not identical. I think the reasoning is that due to DST changes, ‘1 day 2 hours’ is more specific than its conversion to ’26 hours’ (or 25 or 27 at DST change).
And since you’re not converting the number of days in to_char, that information gets lost.
That problem doesn’t seem to arise in the OP’s question (as far as I understand his question), he does have dates to base the intervals on. However, converting the differences in dates to intervals decouples the difference from the dates (the intervals could, for example, subsequently be added to an entirely different date) and he ends up in the same boat.
It would seem that the way to do this is to convert the difference to (seconds since) epoch and do the math to convert that to a character string yourself.
See for example:
https://stackoverflow.com/questions/341384/how-to-convert-an-interval-like-1-day-013000-into-253000
That seems unnecessarily complicated, perhaps there is/could be a more convenient method? I’m sort of thinking of a "relative timestamp offset" type, that tracks an exact difference relative to a given timestamp?
Alban Hertroys
--
There is always an exception to always.
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: haramrae@gmail.com, ken.tanzer@gmail.com, jlabaezarangel@gmail.com
Subject: Re: Duda sobre como imprimir un campo INTERVAL
In-Reply-To: <7DD6FF46-1F92-4E6B-A288-45A26021EE75@gmail.com>
References: <CAJKTcg9jLfH+-v8TS2gV=vZzqjLZ5cYyMVUtGxwyRitER5htMg@mail.gmail.com> <CAD3a31Xo1zGKRDNNQizU8123WvcjN_zheCHU9V+c_gR5GM1ayw@mail.gmail.com> <7DD6FF46-1F92-4E6B-A288-45A26021EE75@gmail.com>
* 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