Re: [BUGS] extract(epoch from infinity) is not 0

On 14 July 2011 08:16, Robert Haas <robertmhaas@gmail.com> wrote:
> On Jul 13, 2011, at 4:21 PM, Brendan Jurd <direvus@gmail.com> wrote:
>> Well, for example, how do you go about answering the question "what is
>> the day-of-month of the infinite timestamp?"  The question is
>> nonsense; it doesn't have a defined day of month, so I think we should
>> be returning NULL or throwing an error.  Returning zero is definitely
>> wrong.  I think throwing an error is the better way to go, as the user
>> probably didn't intend to ask an incoherent question.
>>
>> It makes sense to special-case 'epoch' because it effectively converts
>> the operation into interval math; if we ask "how many seconds from
>> 1970-01-01 00:00 UTC until the infinite timestamp?" the answer is
>> genuinely "infinite seconds".  So +1 for the proposed change for
>> epoch, and let's throw an error for the other date fields instead of
>> returning zero.
>
> I'd rather we avoid throwing an error, because that sometimes forces people who want to handle that case to use a
subtransactionto catch it, which is quite slow. 

SELECT CASE WHEN isfinite(ts) THEN extract(day from ts) ELSE NULL END

Cheers,
BJ