Skip site navigation (1) Skip section navigation (2)

Re: Julian date output?

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Cc: rmw256(at)hotmail(dot)com
Subject: Re: Julian date output?
Date: 2011-07-16 11:48:06
Message-ID: 9e13ea83566e57c8579967778212c27c@biglumber.com (view raw or flat)
Thread:
Lists: pgsql-novice
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
NotDashEscaped: You need GnuPG to verify this message


> I can see how to specify a Julian date, (J7395), 
> but how do I get a "random" date converted to a Julian date?

Use the 'J' modifier of the built-in TO_CHAR function:

SELECT TO_CHAR(now(), 'J');

SELECT TO_CHAR(myfield, 'J') FROM mytable;

See:
http://www.postgresql.org/docs/current/static/functions-formatting.html

If you are using this for astronomical purposes, keep in mind that 
Postgres flips a Julian day at midnight, not noon!

If you are using this as a way to do arithmetic on dates, there 
are much better ways:

> On a related note, when taking the difference between two dates, 
> how do I "force" the result to be in days (only)?

The result of subtracting two dates in Postgres is already 
the number of days, as an integer, so one way is to simply force 
the timestamps to dates first:

SELECT now()::date - '1970-01-01 12:34'::date
 ?column? 
----------
    15171
(1 row)

Alternatively, you can use the EXTRACT function:

SELECT EXTRACT('days' FROM (now() - '1970-01-01 12:34'::timestamp));
 date_part 
-----------
     15170
(1 row)

Note the difference in result due to where the rounding occurs.

-- 
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201107160740
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk4hekcACgkQvJuQZxSWSsjsMwCfSPvbFguYWNnxe5ZCc95bHH71
CP0AoPIiR+9MkjQLsJrKnAMUWNa/3OZE
=Cx1n
-----END PGP SIGNATURE-----



In response to

pgsql-novice by date

Next:From: Michèle GarocheDate: 2011-07-17 06:24:58
Subject: Re: Request over partition and join
Previous:From: Roy's EmailDate: 2011-07-16 03:43:41
Subject: Julian date output?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group