Re: Julian date output?

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