Thread: time conversion fuinctions

Hi All,

Is this the expected result?

The question particularly apply to the last SELECT. I'd expected it to
return boolean value just like in the second example below. It returns
nothing instead, and does not rise an error either. Is this the correct
behavior?

But also, is it correct for a function date(timestamp) to return one row
of *nothing*?

----------------postgres v8.1.4----------------------------
# SELECT timestamp 'today';
      timestamp
---------------------
 2006-10-07 00:00:00
(1 row)

# SELECT date(timestamp 'today') <> date(timestamp 'yesterday');
 ?column?
----------
 t
(1 row)

# SELECT timestamp 'infinity';
 timestamp
-----------
 infinity
(1 row)

# SELECT timestamp '-infinity';
 timestamp
-----------
 -infinity
(1 row)

# SELECT date(timestamp 'infinity');
 date
------

(1 row)

# SELECT date(timestamp '-infinity');
 date
------

(1 row)

# SELECT date(timestamp '-infinity') <> date(timestamp 'infinity');
 ?column?
----------

(1 row)
---------------------END----------------------



--
-R

Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> Is this the expected result?

timestamp_date() currently returns NULL if the timestamp is infinity.
Since we don't have any representation for infinity in the date type,
this is pretty much its only alternative other than throwing an error
(which might indeed be a saner behavior).  There is a TODO item to
support +/-infinity in dates, which would be the correct long-term
solution.

            regards, tom lane

Tom Lane wrote:
> Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
>
>>Is this the expected result?
>
>
> timestamp_date() currently returns NULL if the timestamp is infinity.
> Since we don't have any representation for infinity in the date type,
> this is pretty much its only alternative other than throwing an error
> (which might indeed be a saner behavior).  There is a TODO item to
> support +/-infinity in dates, which would be the correct long-term
> solution.
>

"long-term", indeed.

b

On Sat, 2006-10-07 at 11:57 -0400, Tom Lane wrote:
> Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> > Is this the expected result?
>
> timestamp_date() currently returns NULL if the timestamp is infinity.
> Since we don't have any representation for infinity in the date type,

I understand, you mean 'internal representation'. Since it's quite
obvious, that 'external representation' can be '+/-infinity', just like
it's the case with the timestamp.

> this is pretty much its only alternative other than throwing an error
> (which might indeed be a saner behavior).  There is a TODO item to
> support +/-infinity in dates, which would be the correct long-term
> solution.

I see. Provided the internal representation must change to get there,
some immediate measure (like faulting the statement) might be a good
idea ideed.
--
-R