Obtaining the Julian Day from a date

**Phil Endecott** · Nov 23 '05, 02:03 AM

Re: Obtaining the Julian Day from a date

Karl O. Pinc wrote:[color=blue]
> What's the best way to obtain the Julian day from a postgresql
> date?[/color]

=> select to_char('17 may 1970'::date,'J' );
to_char
---------
2440724

--Phil.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

**Bruno Wolff III** · Nov 23 '05, 02:03 AM

Re: Obtaining the Julian Day from a date

On Thu, Sep 09, 2004 at 12:35:14 -0500,
"Karl O. Pinc" <kop@meme.com > wrote:[color=blue]
> Hi,
>
> What's the best way to obtain the Julian day from a postgresql
> date?
>
> 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)
>
> I'm doing some date arithmetic with 1 day intervals and want
> to, for example, round to the even Julian day. I suppose
> I could always take the interval from julian day zero
> and then divide by the number of seconds in a day, but that
> sounds both brutal and potentially inaccurate due to leap
> seconds and so forth.
>
> There's mention of being able to do this in the list archives,
> but nobody says how it's actually done.[/color]

You might be interested to know that there are operators that combine
date and integer types that might be usable directly instead of
converting to Julian days.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Karl O. Pinc** · Nov 23 '05, 02:03 AM

Re: Obtaining the Julian Day from a date

On 2004.09.09 14:11 Bruno Wolff III wrote:[color=blue]
> On Thu, Sep 09, 2004 at 12:35:14 -0500,
> "Karl O. Pinc" <kop@meme.com > wrote:[color=green]
> > Hi,
> >
> > What's the best way to obtain the Julian day from a postgresql
> > date?
> >
> > 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)
> >
> > I'm doing some date arithmetic with 1 day intervals and want
> > to, for example, round to the even Julian day.[/color]
>
> You might be interested to know that there are operators that combine
> date and integer types that might be usable directly instead of
> converting to Julian days.[/color]

Thanks. (It's not documented for 7.3 but works. It is documented
for 7.4.)

Unfortunately modulo (%) does not operate on dates so I still need

to convert to Julian day. :-( I need to know where I am within a
regular repeating interval. Mostly, in my case, modulo 2.
(We arbitrarly decided to begin our interval on Julian Day 0.)

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Bruno Wolff III** · Nov 23 '05, 02:04 AM

Re: Obtaining the Julian Day from a date

On Thu, Sep 09, 2004 at 16:32:18 -0500,
"Karl O. Pinc" <kop@meme.com > wrote:[color=blue]
>
> Unfortunately modulo (%) does not operate on dates so I still need
>
> to convert to Julian day. :-( I need to know where I am within a
> regular repeating interval. Mostly, in my case, modulo 2.
> (We arbitrarly decided to begin our interval on Julian Day 0.)[/color]

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.

You could also do the subtraction before storing the data if you want
to keep it internally as Julian days.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Karl O. Pinc** · Nov 23 '05, 02:05 AM

Re: Obtaining the Julian Day from a date

On 2004.09.10 20:32 Bruno Wolff III wrote:
[color=blue]
> 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.[/color]

I've been doing:

CAST (to_char(date, 'J') AS INT)

but your way seems faster. Is it?

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

**Tom Lane** · Nov 23 '05, 02:05 AM

Re: Obtaining the Julian Day from a date

"Karl O. Pinc" <kop@meme.com > writes:[color=blue]
> On 2004.09.10 20:32 Bruno Wolff III wrote:[color=green]
>> 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.[/color][/color]
[color=blue]
> I've been doing:
> CAST (to_char(date, 'J') AS INT)
> but your way seems faster. Is it?[/color]

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.

Another advantage is that you can equally easily adopt *any* base date,
it doesn't have to be Julian day 0. This would let you shift between
say Monday and Sunday as start-of-the-week without extra logic.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

**Karl O. Pinc** · Nov 23 '05, 02:05 AM

Re: Obtaining the Julian Day from a date

On 2004.09.11 10:33 Tom Lane wrote:[color=blue]
> "Karl O. Pinc" <kop@meme.com > writes:[color=green]
> > On 2004.09.10 20:32 Bruno Wolff III wrote:[color=darkred]
> >> 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.[/color][/color]
>[color=green]
> > I've been doing:
> > CAST (to_char(date, 'J') AS INT)
> > but your way seems faster. Is it?[/color]
>
> 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.[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

**Tom Lane** · Nov 23 '05, 02:05 AM

Re: Obtaining the Julian Day from a date

"Karl O. Pinc" <kop@meme.com > writes:[color=blue]
> RETURN day_zero + CAST (julian_day || ' days' AS INTERVAL);[/color]

That's certainly the hard way. Just use the date + integer operator
(ie, "RETURN day_zero + julian_day").
[color=blue]
> day_zero CONSTANT DATE := CAST (0 AS DATE);[/color]

Does that really work? I get

regression=# select CAST (0 AS DATE);
ERROR: cannot cast type integer to date

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

PostgreSQL: Not Found

http://www.postgresql.org/docs/faqs/FAQ.html

**Karl O. Pinc** · Nov 23 '05, 02:05 AM

Re: Obtaining the Julian Day from a date

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

Doh! Thanks. I'm stuck on intervals.
[color=blue]
>[color=green]
> > day_zero CONSTANT DATE := CAST (0 AS DATE);[/color]
>
> Does that really work? I get
>
> regression=# select CAST (0 AS DATE);
> ERROR: cannot cast type integer to date[/color]

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('172106 0', '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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

**Tom Lane** · Nov 23 '05, 02:05 AM

Re: Obtaining the Julian Day from a date

"Karl O. Pinc" <kop@meme.com > writes:[color=blue]
> Are there external representations of BC dates?[/color]

Of course.

regression=# select to_char(date '4714-11-24 BC', 'J');
to_char
---------
0
(1 row)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

PostgreSQL: PostgreSQL Mailing List Archives

http://archives.postgresql.org

**Karl O. Pinc** · Nov 23 '05, 02:05 AM

Re: Obtaining the Julian Day from a date

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

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(CURREN T_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'
[color=blue]
>
> 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)[/color]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Obtaining the Julian Day from a date

Obtaining the Julian Day from a date

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment