On 16 Jul 2012, at 17:57, Daniele Varrazzo wrote: > On Mon, Jul 16, 2012 at 3:56 PM, Craig Ringer <ringerc@xxxxxxxxxxxxx> wrote: >> On 07/16/2012 07:41 PM, Alban Hertroys wrote: >>>>> >>>>> BTW, that second value looks a whole lot like a poorly thought out > >> Yup. The 'infinity' value doesn't play well with all database access APIs >> and languages, though. > > It doesn't even play well with PostgreSQL's extract(). I reported it > some times ago but as of 9.1.4 it has not been fixed. > > =# select extract(epoch from 'infinity'::timestamp); > date_part > ----------- > 0 > > This makes 'infinity' a problematic choice in any application > requiring a mapping between dates and reals, such as when using > intervals in gist indexes. Well yeah, obviously. I don't think many mathematicians have wrapped their brain around questions like what day of the week infinity is and whether it's a working day or not. Perhaps Douglas Adams did, in which case it was probably a Tuesday. I'm just saying, most of the date-parts that extract can retrieve from a timestamp are meaningless with infinity. But, they are also be meaningless with a placeholder date like 31-12-2999. That said, if it were up to me to decide what the proper epoch value would be for infinity, I'd say NULL - it is unknown as computers simply can't count far enough. It's probably only a matter of time until someone thinks of a solution for that though. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general