Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Petr Fedorov <petr.fedorov@phystech.edu>, pgsql-bugs@postgresql.org
Date: 2019-12-01T22:59:11Z
Lists: pgsql-bugs, pgsql-hackers 
Thomas Munro <thomas.munro@gmail.com> writes:
> On Sat, Nov 30, 2019 at 10:28 PM Petr Fedorov <petr.fedorov@phystech.edu> wrote:
>> Obviously, it is due to the fact that extract epoch returns double
>> precision which in turn has 15 decimal digits precision.

> I guess this deviation from the SQL standard ("exact numeric") made
> sense when PostgreSQL used double for timestamps, but would break a
> lot of queries if we changed it.

Hmmm ... well, now that you mention it, would it really break things
if we made it return numeric?  There's an implicit cast to float8,
so it seems like queries requiring that type would still work.

There might be a performance-related argument against switching,
perhaps.

			regards, tom lane

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.