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: Vik Fearing <vik@postgresfriends.org>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>, Thomas Munro <thomas.munro@gmail.com>, Petr Fedorov <petr.fedorov@phystech.edu>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2020-05-25T17:07:30Z
Lists: pgsql-bugs, pgsql-hackers 
Vik Fearing <vik@postgresfriends.org> writes:
> On 5/25/20 6:40 PM, Tom Lane wrote:
>> While at it, maybe we could
>> fix things so that the syntax reverse-lists the same way instead
>> of injecting Postgres-isms...

> I'm not sure what this means.

This:

regression=# create view myview as select extract(year from current_timestamp) as y;
CREATE VIEW
regression=# \d+ myview
...
View definition:
 SELECT date_part('year'::text, CURRENT_TIMESTAMP) AS y;

What had been a 100% spec-compliant view definition is now quite
Postgres-specific.  I fixed some similar problems in 0bb51aa96 (before
that, the CURRENT_TIMESTAMP part would've reverse-listed differently
too); but I didn't tackle EXTRACT(), SUBSTRING(), and other cases.

I'm not claiming that we really need to fix all of those.  But if we are
going to pick nits about which data type EXTRACT() returns then I think
it's legit to worry about its reverse-list representation at the same
time ... especially if we must touch the grammar's translation anyway.

			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.