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

út 4. 8. 2020 v 16:08 odesílatel Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> napsal:

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

I like a idea to have d date variant of date_part

Pavel


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