Horton

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

Thomas Munro <thomas.munro@gmail.com>

View thread View on postgr.es
From: Thomas Munro <thomas.munro@gmail.com>
To: Petr Fedorov <petr.fedorov@phystech.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@postgresql.org
Date: 2019-12-02T22:52:31Z
Lists: pgsql-bugs, pgsql-hackers 
On Tue, Dec 3, 2019 at 12:08 AM Petr Fedorov <petr.fedorov@phystech.edu> wrote:
> It appears that extract epoch returns double precision, not float8.  And
> the program below seems to be demonstrating that there are enough
> 'floating-point numbers' as defined by  IEEE-754 to represent
> 1000000000.000021 precisely enough:

Double precision and float8 are different names for the same type in PostgreSQL.

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

It's also wrong because binary floating point numbers can't represent
0.000001 (one microsecond represented as seconds) exactly, and that's
our unit of counting for timestamps.  You can get pretty far by
thinking of the decimal number you see on the screen as the true
number and the double as a fuzzy internal storage or transport that
does the job just fine due to the round trip conversion guarantee
provided by DBL_DIG, but the double is still going to have the wrong
value in some cases.  As soon as you start doing any arithmetic or
comparisons with the double directly, interesting things can start to
happen to make the error visible and break things; for example
0.1::float8 + 0.2::float8 = 0.3::float8 is false.

> And since it works correctly on v12 for this particular date may be all
> what is needed it to verify that it works for the other dates too! For
> example what was changed in v12 (comparing to 11.6 I use) so extract
> epoch works correctly?

PostgreSQL 12 adopted a different algorithm[1] for converting float8
to text that can affect how many digits are shown, as Tom explained.
The manual has some notes about it[2].

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=02ddd499322ab6f2f0d58692955dc9633c2150fc
[2] https://www.postgresql.org/docs/12/datatype-numeric.html#DATATYPE-FLOAT

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.