Hackers! I'd like to raise a topic about extracting fields from infinite timestamps, so much more that it is mentioned in the TODO list: "Determine how to represent date/time field extraction on infinite timestamps".
Currently extracting any field from 'infinity'::TIMESTAMP[TZ] gives result "0" as a mark it has "special" input value. The most confusing case is 'epoch' field: returning "0" from "infinity" means the same thing as returning "0" from "1970-01-01+00". Returning zero in most other cases is only slightly less confusing (may be because for me they are less often used). For example, what about "SELECT EXTRACT(DOW FROM TIMESTAMP 'Infinity')" with result 0, as if it is Sunday? The same thing with fields: decade, hour, minute, seconds, microseconds, milliseconds, timezone, timezone_hour, timezone_minute. Also for "millennium" and "year" (with the note "Keep in mind there is no 0 AD") current returning value is _between_ allowed values, but disallowed. http://www.postgresql.org/docs/9.5/static/functions-datetime.html There was a discussion ended in nothing. It began at: http://www.postgresql.org/message-id/ca+mi_8bda-fnev9ixeubnqhvacwzbyhhkwoxpqfbca9edpp...@mail.gmail.com Discussants agreed change is necessary, but couldn't decide what behavior is preferred: throwing an error or returning NULL, NaN or +/- infinity. My thoughts about that cases: * Throwing an error: prefer to avoid it according to http://www.postgresql.org/message-id/73a5666e-2d40-457e-9dff-248895db7...@gmail.com * NULL: it is "absence of any value", i.e. it could be returned iff input value is NULL (in the other case it is not better than returning 0). * NaN: it could be returned if value is outside current axe (like complex value), but it is not the case. In a parallel discussion ("converting between infinity timestamp and float8 (epoch)") http://www.postgresql.org/message-id/cadakt-icuesh16ulocxbr-dkpcvwtuje4jwxnkdajaawp6j...@mail.gmail.com There was interesting thought to make difference between monotonic values (century, decade, epoch, isoyear, millennium and year) and oscillating values (day, dow, doy, hour, isodow, microseconds, milliseconds, minute, month, quarter, second and week). An argument is for monotonic values +/- infinity has a sense, but not for oscillating ones. But for oscillating values NULL was proposed, that (IMHO) is not a good idea (see above). I think changing current mark "input value is not finite" allows an app layer (which knows which field it tries to fetch from timestamp[tz]) to handle extracted value correctly. For oscillating values there can be the same values as for monotonic values, because you can't mix them up. The end of the parallel discussion (with the most important thoughts) at http://www.postgresql.org/message-id/4efcfd1c.8040...@archidevsys.co.nz So I think +/- infinity is the best returning value for all fields. The attached patch contains changes in timestamp_part and timestamptz_part and tests for them. I doubt whether it can be backpatched (according to team's rules) or not, but the patch can be applied down to 9.2 without conflicts and passes tests. Unfortunately, on 9.1 proposed test fails because "SELECT EXTRACT(EPOCH FROM DATE '1970-01-01')" gives "28800" instead of "0". Before 9.2 it was time zone-related. -- Best regards, Vitaly Burovoy.
Description: Binary data
-- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers