pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: Intervals and ISO 8601 duration
Date: Thu, 12 Jan 2023 10:08:14 -0500
Message-ID: <953416.1673536094@sss.pgh.pa.us> (raw)
In-Reply-To: <AM9P191MB1286AD77B5FBAF4640E7274AB0FD9@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM>
References: <AM9P191MB1286AD77B5FBAF4640E7274AB0FD9@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM>

Sebastien Flaesch <sebastien.flaesch@4js.com> writes:
> PostgreSQL has the INTERVAL type, which can be defined with fields such as:
> INTERVAL YEAR TO MONTH    (year-month class)
> INTERVAL DAY TO SECOND(p)   (day-second class)

You can also say just INTERVAL, without any of the restrictions.

> It's not possible to define an INTERVAL YEAR TO SECOND(p), which makes
> sense,

It's not so much that it doesn't make sense as that the SQL standard
doesn't have such a spelling.  They enumerate a few allowed combinations
(I think that no-modifiers is one of them), and we accept those for
pro forma syntax compliance.

> Should the following convert to a day-second interval?

> test1=> select cast('P2Y10M15DT10H30M20S' as interval day to second);
>              interval
> ----------------------------------
>  2 years 10 mons 15 days 10:30:20
> (1 row)

> Should PostgreSQL not raise an SQL error in above cases?

We regard these modifiers as similar to precision restrictions in
numerics and timestamps: we will round off low-order fields to
match the typmod, but we will not throw away high-order fields.

This probably doesn't match the SQL spec in detail, but the
details of their datetime types are sufficiently brain-dead
that we've never worried about that too much (eg, they still
don't have a model for daylight-savings time, last I checked).

What Postgres actually stores for an interval is three fields:
months, days, and microseconds.  If we're forced to interconvert
between those units, we use 30 days = 1 month and 24 hours = 1 day,
but it's usually best to avoid doing that.

			regards, tom lane





view thread (24+ messages)  latest in thread

Message-ID: <953416.1673536094@sss.pgh.pa.us>
Permalink:  ../953416.1673536094@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/953416.1673536094@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, sebastien.flaesch@4js.com
  Subject: Re: Intervals and ISO 8601 duration
  In-Reply-To: <953416.1673536094@sss.pgh.pa.us>
  References: <AM9P191MB1286AD77B5FBAF4640E7274AB0FD9@AM9P191MB1286.EURP191.PROD.OUTLOOK.COM> <953416.1673536094@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