Re: Obtaining the Julian Day from a date

On 2004.09.11 14:02 Karl O. Pinc wrote:
>
> On 2004.09.11 13:09 Tom Lane wrote:
>> "Karl O. Pinc" <kop@meme.com> writes:
>
>>
>> >      day_zero CONSTANT DATE := CAST (0 AS DATE);
>>
>> Does that really work?  I get
>>
>> regression=# select  CAST (0 AS DATE);
>> ERROR:  cannot cast type integer to date
>
> No.  I'm trying to come up with something that does,
> like the text representation of julian day zero,
> and get odd stuff.

Well, this won't work, or rather it will, but comes up
with the wrong internal value:

     day_zero CONSTANT DATE := TO_DATE(0, ''J'');

This worked, but sheesh:

     day_zero CONSTANT DATE
      := CURRENT_DATE - CAST (to_char(CURRENT_DATE, ''J'') AS INT);

FWIW, I couldn't get the equalivent to work with ''now'' or
now().  There were timezone complaints with now()

ERROR:  Unable to identify an operator '-' for types 'timestamp with
time zone' and 'integer'

and ''now'' just said

ERROR:  Bad date external representation 'now'

>
> PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC
> i386-redhat-linux-gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein