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
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