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(at)meme(dot)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(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein