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

On 2020-05-25 15:28, Peter Eisentraut wrote:
> On 2019-12-02 23:52, Thomas Munro wrote:
>>> 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.
>
> I looked into this (changing the return types of date_part()/extract()
> from float8 to numeric).
>
> One problem (other than perhaps performance, tbd.) is that this would no
> longer allow processing infinite timestamps, since numeric does not
> support infinity. It could be argued that running extract() on infinite
> timestamps isn't very useful, but it's something to consider explicitly.

Now that numeric supports infinity, here is a patch that changes the
return types of date_part() to numeric. It's not meant to be a final
version, but it is useful for discussing a few things.

The internal implementation could be made a bit more elegant if we had
variants of int4_numeric() and int8_numeric() that don't have to go
through fmgr. This would also help in other areas of the code. There
are probably also other ways in which the internals could be made more
compact; I just converted them fairly directly.

When extracting seconds or microseconds, I made it always produce 6 or 3
decimal places, even if they are zero. I don't know if we want that or
what behavior we want. That's what all the changes in the regression
tests are about. Everything else passes unchanged.

The 'julian' field is a bit of a mystery. First of all it's not
documented. The regression tests only test the rounded output, perhaps
to avoid floating point differences. When you do date_part('julian',
date), then you get a correct Julian Day. But date_part('julian',
timestamp[tz]) gives incorrect Julian Date values that are off by 12
hours. My patch doesn't change that, I just noticed when I took away
the round() call in the regression tests. Those calls now produce a
different number of decimal places.

It might make sense to make date_part(..., date) a separate C function
instead of an SQL wrapper around date_part(..., timestamp). That could
return integer and could reject nonsensical fields such as "minute".
Then we could also make a less contorted implementation of
date_part('julian', date) that matches to_char(date, 'J') and remove the
incorrect implementation of date_part('julian', timestamp).

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services