From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
Cc: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>, Chris Bartlett <c(dot)bartlett(at)paradise(dot)net(dot)nz>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Can't figure out how to use now() in default for tsrange column (PG 9.2) |
Date: | 2012-07-16 17:44:08 |
Message-ID: | 0DB28A00-2921-4CA2-B47C-65AD388ED8E1@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 16 Jul 2012, at 17:57, Daniele Varrazzo wrote:
> On Mon, Jul 16, 2012 at 3:56 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-07-16 18:08:44 | Re: Can't figure out how to use now() in default for tsrange column (PG 9.2) |
Previous Message | Pavel Stehule | 2012-07-16 16:50:25 | Re: Create stored procedure from C#.net |