agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: John Lumby <johnlumby@hotmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: value returned by EXTRACT, date_part
Date: Fri, 28 Aug 2020 21:39:33 -0400
Message-ID: <3517945.1598665173@sss.pgh.pa.us> (raw)
In-Reply-To: <DM6PR06MB5562A231945B39D33623849BA3520@DM6PR06MB5562.namprd06.prod.outlook.com>
References: <DM6PR06MB5562115198C798B1C06B32DBA3520@DM6PR06MB5562.namprd06.prod.outlook.com>
<CAKFQuwZBu3Hp__XKSWeBeqyep=UVd9-4x9nnLvfxxAW3hgCJfg@mail.gmail.com>
<DM6PR06MB5562A231945B39D33623849BA3520@DM6PR06MB5562.namprd06.prod.outlook.com>
John Lumby <johnlumby@hotmail.com> <DM6PR06MB5562A231945B39D33623849BA3520@DM6PR06MB5562.namprd06.prod.outlook.com> writes:
> But also (and probably not easily changed now) it would make EXTRACT
> more generally useful if it included a fractional part for every field
> type where meaningful.
> Is there any reason why it should not?
The SQL spec says otherwise.
Section 4.6.2 "Datetimes" in SQL:2011 defines timestamps as containing
the six fields year, month, day, hour, minute, second (dates contain
only the first three, times only the second three, but otherwise are
similar), plus possibly timezone_hour and timezone_minute. All of
these except the seconds field are specified to be integers.
Furthermore, the EXTRACT function is specifically defined to return
one of these fields.
4.6.3 "Intervals" lays down basically the same sorts of rules for
intervals: they are made of component fields and only the seconds
field can have a fractional part.
PG does offer a nonstandard EPOCH "field" in EXTRACT, which tries
to convert the timestamp or interval as a whole to some number of
seconds. Possibly you could make use of that, perhaps after first
applying date_trunc, to get what you're after. The whole enterprise
is pretty shaky though; for example you cannot convert months to days
or vice versa without making fundamentally-indefensible assumptions.
regards, tom lane
view thread (6+ messages) latest in thread
Message-ID: <3517945.1598665173@sss.pgh.pa.us>
Permalink: ../3517945.1598665173@sss.pgh.pa.us/
Also on: postgresql.org/message-id/3517945.1598665173@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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, johnlumby@hotmail.com, david.g.johnston@gmail.com, pgsql-sql@lists.postgresql.org
Subject: Re: value returned by EXTRACT, date_part
In-Reply-To: <3517945.1598665173@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox