pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ken Tanzer <ken.tanzer@gmail.com>
Cc: Alban Hertroys <haramrae@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 17:22:25 -0500
Message-ID: <2655245.1668896545@sss.pgh.pa.us> (raw)
In-Reply-To: <CAD3a31VET+Z7D=9hsa2=1FuB141OoG9F_vzF=hHa0eSPgBOK2A@mail.gmail.com>
References: <CAJKTcg9jLfH+-v8TS2gV=vZzqjLZ5cYyMVUtGxwyRitER5htMg@mail.gmail.com>
	<CAD3a31Xo1zGKRDNNQizU8123WvcjN_zheCHU9V+c_gR5GM1ayw@mail.gmail.com>
	<7DD6FF46-1F92-4E6B-A288-45A26021EE75@gmail.com>
	<CAD3a31VET+Z7D=9hsa2=1FuB141OoG9F_vzF=hHa0eSPgBOK2A@mail.gmail.com>

Ken Tanzer <ken.tanzer@gmail.com> writes:
> Thanks.  I could understand that they're not identical.   But then what's
> going on where Postgres evaluates them as equal?  (i1=i2 above.)  Are the
> two intervals getting cast or converted to something else before they are
> compared, with whatever makes them non-identical getting lost in the
> conversion?

Hmm ... the code is pretty clear about what it thinks it's doing,
but I'm not sure if this info exists anywhere in the user-facing docs:

 * Interval comparison is based on converting interval values to a linear
 * representation expressed in the units of the time field (microseconds,
 * in the case of integer timestamps) with days assumed to be always 24 hours
 * and months assumed to be always 30 days.

So once '1 day 2 hours' and '26 hours' are flattened into this linear
representation, they are indeed "equal".  There's precedent elsewhere
for values that are "equal" but not identical, so it's not a bug,
even if it's not the semantics you'd want for some particular use-case.

I think the main factor behind having done it this way is that we need
a linear sort order if we want to support btree indexes or ORDER BY
on intervals.

You can use justify_hours() to get from '26 hours' to '1 day 2 hours'.
I'm not sure if there's a compact way to go the other direction,
though you could always use extract() to get the components and
sum them up.

			regards, tom lane





view thread (5+ messages)

Message-ID: <2655245.1668896545@sss.pgh.pa.us>
Permalink:  ../2655245.1668896545@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2655245.1668896545@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, ken.tanzer@gmail.com, haramrae@gmail.com, jlabaezarangel@gmail.com
  Subject: Re: Duda sobre como imprimir un campo INTERVAL
  In-Reply-To: <2655245.1668896545@sss.pgh.pa.us>
  References: <CAJKTcg9jLfH+-v8TS2gV=vZzqjLZ5cYyMVUtGxwyRitER5htMg@mail.gmail.com> <CAD3a31Xo1zGKRDNNQizU8123WvcjN_zheCHU9V+c_gR5GM1ayw@mail.gmail.com> <7DD6FF46-1F92-4E6B-A288-45A26021EE75@gmail.com> <CAD3a31VET+Z7D=9hsa2=1FuB141OoG9F_vzF=hHa0eSPgBOK2A@mail.gmail.com> <2655245.1668896545@sss.pgh.pa.us>

* 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