agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedvalue returned by EXTRACT, date_part
6+ messages / 3 participants
[nested] [flat]
* value returned by EXTRACT, date_part
@ 2020-08-28 17:43 John Lumby <johnlumby@hotmail.com>
2020-08-28 19:44 ` Re: value returned by EXTRACT, date_part David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: John Lumby @ 2020-08-28 17:43 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
I notice that although EXTRACT(field FROM source) returns a value of
type double-precision,
for some field types, it apparently rounds the value to an integer,
(even though the exact value is not integral),
whereas for others it returns the exact value including any fractional
part un-rounded. Examples can be seen in the book :
SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
Result: 40
rounded down from 40.0.0006944444444444445
SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16
20:38:40.12-08');
Result: 982384720.12
un-rounded
This seems inconsistent to me - and undocumented as far as I can tell.
Is there any reason for this? And can it be relied on in programs?
Cheers, John
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value returned by EXTRACT, date_part
2020-08-28 17:43 value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
@ 2020-08-28 19:44 ` David G. Johnston <david.g.johnston@gmail.com>
2020-08-28 21:18 ` Re: value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2020-08-28 19:44 UTC (permalink / raw)
To: John Lumby <johnlumby@hotmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On Fri, Aug 28, 2020 at 10:43 AM John Lumby <johnlumby@hotmail.com> wrote:
> I notice that although EXTRACT(field FROM source) returns a value of
> type double-precision,
>
> for some field types, it apparently rounds the value to an integer,
> (even though the exact value is not integral),
>
> whereas for others it returns the exact value including any fractional
> part un-rounded. Examples can be seen in the book :
>
>
> SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
> Result: 40
>
> rounded down from 40.0.0006944444444444445
>
>
> SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16
> 20:38:40.12-08');
> Result: 982384720.12
>
> un-rounded
>
epoch is defined as "number of seconds since" and seconds is defined to
include fractional parts - so one can argue that the indirection is bad but
it is documented.
This seems inconsistent to me - and undocumented as far as I can tell.
>
> Is there any reason for this? And can it be relied on in programs?
>
The ones that return fractions are documented as such (I think just
seconds, and its relative epoch).
It does it that way because of the nature of writing overloaded functions.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value returned by EXTRACT, date_part
2020-08-28 17:43 value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
2020-08-28 19:44 ` Re: value returned by EXTRACT, date_part David G. Johnston <david.g.johnston@gmail.com>
@ 2020-08-28 21:18 ` John Lumby <johnlumby@hotmail.com>
2020-08-29 01:39 ` Re: value returned by EXTRACT, date_part Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: John Lumby @ 2020-08-28 21:18 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
On 2020-08-28 15:44, David G. Johnston wrote:
> On Fri, Aug 28, 2020 at 10:43 AM John Lumby <johnlumby@hotmail.com
> <mailto:johnlumby@hotmail.com>> wrote:
>
>
> SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
> Result: 40
>
> rounded down from 40.0006944444444444445
>
>
> SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16
> 20:38:40.12-08');
> Result: 982384720.12
>
> un-rounded
>
>
> epoch is defined as "number of seconds since" and seconds is defined
> to include fractional parts - so one can argue that the indirection is
> bad but it is documented.
>
Thanks David, I had missed the mention of fractional part on seconds
and milliseconds.
But I think you had to dance pretty vigorously to make that defence
stand up.
In the description of epoch, the word "seconds" is not in "code" font
so one would presume it is the ordinary meaning, not a reference to
another field.
>
> The ones that return fractions are documented as such (I think just
> seconds, and its relative epoch).
>
I think it could be made clearer , in particular :
. where it is stated that the returned type is double precision,
state that values are rounded down unless explicitly described as
including a fractional part
. for "epoch" add the statement that this includes a fractional part.
>
> David J.
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?
For example, after trying a few ideas I *think* postgresql (v12)
does not offer any way to get an exact, double-precision rendition of
the interval in the above example
What I want is something like
SELECT EXTRACT(<anyunit> FROM INTERVAL '40 days 1 minute');
returns a double-precision type with a value representing a stretch of
time exactly equal to 40.0006944444444444445 days or 57601.0 minutes
EXTRACT(DAY FROM INTERVAL '40 days 1 minute') doesn't as we have seen
date_trunc('minute' , INTERVAL '40 days 1 minute') doesn't
But I suppose it depends on one's interpretation of what a "subfield" of
an interval is.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value returned by EXTRACT, date_part
2020-08-28 17:43 value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
2020-08-28 19:44 ` Re: value returned by EXTRACT, date_part David G. Johnston <david.g.johnston@gmail.com>
2020-08-28 21:18 ` Re: value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
@ 2020-08-29 01:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-29 13:13 ` Re: value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2020-08-29 01:39 UTC (permalink / raw)
To: John Lumby <johnlumby@hotmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value returned by EXTRACT, date_part
2020-08-28 17:43 value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
2020-08-28 19:44 ` Re: value returned by EXTRACT, date_part David G. Johnston <david.g.johnston@gmail.com>
2020-08-28 21:18 ` Re: value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
2020-08-29 01:39 ` Re: value returned by EXTRACT, date_part Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-08-29 13:13 ` John Lumby <johnlumby@hotmail.com>
2020-08-29 16:04 ` Re: value returned by EXTRACT, date_part Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: John Lumby @ 2020-08-29 13:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
Thanks Tom
On 2020-08-28 21:39, Tom Lane wrote:
>
> 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.
What is not clear to me is how the "components" (aka "subfields") of an
interval are defined.
e.g.
SELECT EXTRACT(days FROM INTERVAL '1 year 35 days 1 minute');
date_part
-----------
35
ok, it takes the interval modulo months (the next higher unit than
the one I requested) and then rounds that down.
But
SELECT EXTRACT(days FROM INTERVAL '400 days 1 minute');
date_part
-----------
400
oh! no it doesn't ...
>
> 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.
Yes! That is exactly what I am looking for. Actually for
simplicity I think I don't need date_trunc;
for this particular case of wanting to find the size of an interval,
it is as simple as always requesting its epoch and working in
double-precision seconds.
Thanks!
>
> regards, tom lane
> .
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: value returned by EXTRACT, date_part
2020-08-28 17:43 value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
2020-08-28 19:44 ` Re: value returned by EXTRACT, date_part David G. Johnston <david.g.johnston@gmail.com>
2020-08-28 21:18 ` Re: value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
2020-08-29 01:39 ` Re: value returned by EXTRACT, date_part Tom Lane <tgl@sss.pgh.pa.us>
2020-08-29 13:13 ` Re: value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
@ 2020-08-29 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2020-08-29 16:04 UTC (permalink / raw)
To: John Lumby <johnlumby@hotmail.com>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; pgsql-sql <pgsql-sql@lists.postgresql.org>
John Lumby <johnlumby@hotmail.com> <DM6PR06MB55625C1A9319BC921F4FB4ACA3530@DM6PR06MB5562.namprd06.prod.outlook.com> writes:
> What is not clear to me is how the "components" (aka "subfields") of an
> interval are defined.
The spec says that year,month,day,hour,minute,second are independent
fields. Postgres doesn't implement it quite that way: we treat the
basic fields as being months, days, and (micro)seconds, folding the
other values into those for storage. That's why "14 months" comes out
as "1 year 2 months", for example.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-08-29 16:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-28 17:43 value returned by EXTRACT, date_part John Lumby <johnlumby@hotmail.com>
2020-08-28 19:44 ` David G. Johnston <david.g.johnston@gmail.com>
2020-08-28 21:18 ` John Lumby <johnlumby@hotmail.com>
2020-08-29 01:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-29 13:13 ` John Lumby <johnlumby@hotmail.com>
2020-08-29 16:04 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox