Re: Obtaining the Julian Day from a date - Mailing list pgsql-general

On 2004.09.11 13:09 Tom Lane wrote:
> "Karl O. Pinc" <kop@meme.com> writes:
> >      RETURN day_zero + CAST (julian_day || ' days' AS INTERVAL);
>
> That's certainly the hard way.  Just use the date + integer operator
> (ie, "RETURN day_zero + julian_day").

Doh!  Thanks.  I'm stuck on intervals.

>
> >      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.

babase_test=> select to_date('0', 'J');
     to_date    ---------------
  0001-01-01 BC
(1 row)

babase_test=> select to_char(date '0001-01-01 BC', 'J');
  to_char ---------
  1721060
(1 row)

babase_test=> select to_date('1721060', 'J');
     to_date    ---------------
  0001-01-01 BC
(1 row)

Are there external representations of  BC dates?

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