I can see how to specify a Julian date, (J7395), but how do I get a "random" date converted to a Julian date? Do I need to do the calculation (from 4713 BC) myself?

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

Thanks in advance.

Regards,

- Roy

=*=*=
Always do right.
This will gratify some people and astonish the rest. - Mark Twain

The truth is rarely pure, and never simple. - Oscar Wilde

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
NotDashEscaped: You need GnuPG to verify this message


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/curre...ormatting.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:

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=2529DF...9B906714964AC8
-----BEGIN PGP SIGNATURE-----

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



--
Sent via pgsql-novice mailing list (pgsql-novice (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice