Horton

Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

Peter Eisentraut <peter.eisentraut@2ndquadrant.com>

View thread View on postgr.es
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Thomas Munro <thomas.munro@gmail.com>, Petr Fedorov <petr.fedorov@phystech.edu>, Tom Lane <tgl@sss.pgh.pa.us>
Date: 2020-08-04T14:08:07Z
Lists: pgsql-bugs, pgsql-hackers

Attachments

On 2020-05-25 15:28, Peter Eisentraut wrote:
> On 2019-12-02 23:52, Thomas Munro wrote:
>>> I'm not an expert in floating point math but hopefully it means that no
>>> type change is required - double precision can handle it.
>> Me neither, but the SQL standard requires us to use an exact numeric
>> type, so it's wrong on that level by definition.
> 
> I looked into this (changing the return types of date_part()/extract()
> from float8 to numeric).
> 
> One problem (other than perhaps performance, tbd.) is that this would no
> longer allow processing infinite timestamps, since numeric does not
> support infinity.  It could be argued that running extract() on infinite
> timestamps isn't very useful, but it's something to consider explicitly.

Now that numeric supports infinity, here is a patch that changes the 
return types of date_part() to numeric.  It's not meant to be a final 
version, but it is useful for discussing a few things.

The internal implementation could be made a bit more elegant if we had 
variants of int4_numeric() and int8_numeric() that don't have to go 
through fmgr.  This would also help in other areas of the code.  There 
are probably also other ways in which the internals could be made more 
compact; I just converted them fairly directly.

When extracting seconds or microseconds, I made it always produce 6 or 3 
decimal places, even if they are zero.  I don't know if we want that or 
what behavior we want.  That's what all the changes in the regression 
tests are about.  Everything else passes unchanged.

The 'julian' field is a bit of a mystery.  First of all it's not 
documented.  The regression tests only test the rounded output, perhaps 
to avoid floating point differences.  When you do date_part('julian', 
date), then you get a correct Julian Day.  But date_part('julian', 
timestamp[tz]) gives incorrect Julian Date values that are off by 12 
hours.  My patch doesn't change that, I just noticed when I took away 
the round() call in the regression tests.  Those calls now produce a 
different number of decimal places.

It might make sense to make date_part(..., date) a separate C function 
instead of an SQL wrapper around date_part(..., timestamp).  That could 
return integer and could reject nonsensical fields such as "minute". 
Then we could also make a less contorted implementation of 
date_part('julian', date) that matches to_char(date, 'J') and remove the 
incorrect implementation of date_part('julian', timestamp).

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

  1. Fix inconsistent equalfuncs.c behavior for FuncCall.funcformat.

  2. Doc: fix discussion of how to get real Julian Dates.

  3. Doc: document EXTRACT(JULIAN ...), improve Julian Date explanation.

  4. Change return type of EXTRACT to numeric

  5. Improve our ability to regurgitate SQL-syntax function calls.

  6. Add more tests for EXTRACT of date type

  7. Expose internal function for converting int64 to numeric

  8. Change floating-point output format for improved performance.