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

On Jul 13, 2011, at 4:21 PM, Brendan Jurd <direvus(at)gmail(dot)com> wrote:
> On 14 July 2011 06:58, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
>> I don't find the proposed behavior all that suprising, which the
>> original behavior surely is. I guess the bigger question is whether the
>> values that timestamptz_part() returns for other cases (than epoch)
>> should also be different from 0 when an 'infinity' timestamp is passed.
>> (In other words, why should 0 be the assumed return value here?)
>>
>
> 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 subtransaction to catch it, which is quite slow. If we don't like 0, perhaps NULL or NaN would be better.

...Robert