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
