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: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Steele <david@pgmasters.net>, Pavel Stehule <pavel.stehule@gmail.com>, Thomas Munro <thomas.munro@gmail.com>, Petr Fedorov <petr.fedorov@phystech.edu>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2021-04-01T18:49:45Z
Lists: pgsql-bugs, pgsql-hackers

Attachments

On 19.03.21 21:06, Tom Lane wrote:
> Yeah, I was wondering if we could do something like that, but I hadn't
> got as far as figuring a way to deal with divisors not a multiple of
> NBASE.
> 
> Looking at the proposed code, I wonder if it wouldn't be better to
> define the function as taking the base-10-log of the divisor, so that
> you'd have the number of digits to shift (and the dscale) immediately
> instead of needing repeated integer divisions to get that.

done that way, much simpler now

> Also, the
> risk of intermediate overflow here seems annoying:
> 
> +		if (unlikely(pg_mul_s64_overflow(val1, NBASE/x, &val1)))
> +			elog(ERROR, "overflow");
> 
> Maybe that's unreachable for the ranges of inputs the current patch could
> create, but it seems like it makes the function distinctly less
> general-purpose than one would think from its comment.  Maybe, if that
> overflows, we could handle the failure by making that adjustment after
> we've converted to numeric?

also done

I also figured out a way to combine the float8 and numeric 
implementations so that there is not so much duplication.  Added tests 
to cover all the edge and overflow cases.

I think this is solid now.

The extract(julian from timestamp) is still a bit in the slow mode, but 
as I previously stated, it's not documented and gives the wrong result, 
so it's not clear whether it should be fixed and what it should do.  I 
think I'll register that part as an open item in any case, to see what 
we should do about that.

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.