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: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>, Thomas Munro <thomas.munro@gmail.com>, Petr Fedorov <petr.fedorov@phystech.edu>, Tom Lane <tgl@sss.pgh.pa.us>
Date: 2020-08-12T07:04:07Z
Lists: pgsql-bugs, pgsql-hackers

Attachments

Here is a new patch series version.

I have created a new internal function for converting integers to 
numeric, to make the implementation a bit more elegant and compact.

I have also created a new date_part(..., date) in C, and added more test 
coverage for that.

Other than some of the semantic issues mentioned in the previous 
message, this version looks pretty good to me in principle.

I have done some performance tests to assess the impact of changing from 
float to numeric.  I did tests like this:

create table t1 (a int, b timestamp with time zone);
insert into t1 select generate_series(1, 10000000), current_timestamp + 
random() * interval '1000 days';

select extract(dow from b) from t1 \g /dev/null
select extract(epoch from b) from t1 \g /dev/null

There appears to be about a 20% increase in run time for these tests. 
These are obviously extreme tests, so I think that would be okay.  More 
tests and testing ideas are welcome.

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