Re: Obtaining the Julian Day from a date

On 2004.09.11 10:33 Tom Lane wrote:
> "Karl O. Pinc" <kop@meme.com> writes:
> > On 2004.09.10 20:32 Bruno Wolff III wrote:
> >> If you keep your data in a date field you can get the Julian day
> >> by subtracting the appropiate date. You can then do mod on this
> >> difference.
>
> > I've been doing:
> > CAST (to_char(date, 'J') AS INT)
> > but your way seems faster.  Is it?
>
> Date subtraction is extremely fast (it's really the same as integer
> subtraction), so yes I'd expect it to beat the pants off doing to_char
> and then conversion back to integer.

There seems to be no corresponding quick reverse transformation,
integer (julian day) to date.  (Postgres 7.3.)

  DELCARE
     day_zero CONSTANT DATE := CAST (0 AS DATE);
     julian_day INT;
  BEGIN
     RETURN day_zero + CAST (julian_day || ' days' AS INTERVAL);

seems barely faster than

RETURN TO_DATE(CAST (julian_day AS TEXT), ''J'')

I'd be leery about wacky leap seconds and so forth or I'd
try multiplying days be seconds and cast to interval or something
like that.

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