On Mon, 2012-07-16 at 14:08 -0400, David Johnston wrote:
[------------]
>
> Specific, but unknown (e.g., day of week, month, year, etc...) results could
> return "NaN" though "NULL" is also, probably more, reasonable given the
> context.
>
> The goal would be to use "Infinity" in case where "<>" comparisons are
> common and use "NULL" where "=" comparisons are common.
Is that even possible to implement? (e.g.: "SELECT * FROM log WHERE
start_date <> 'XXXX-YY-ZZ' and end_date = 'ZZZZ-AA-BB'" - when both
start_date and end_date possibly have 'infinity')
Anyway, "NaN" looks quite appealing, particulary since currently:
SELECT date_part('year','infinity'::timestamp ) ;
date_part
-----------
0
(1 row)
... can lead to applications misbehaving in strange ways.
I feal that date_part() on infinity, should behave "similarly to"
division by zero - an exception. But seeing a lot of code obfuscated
with checks for division by zero before doing an opperation, I'd opt for
silently returning a NaN in most cases, with fields like 'year',
'century', 'epoch', etc. returning 'Infinity'.
-R