Julian Dates, abbreviated JD, are simply a continuous count of days since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). They make it easy to compute an integer (whole number) difference between one calendar date and another calendar date.
Vertica uses the JULIAN_DAY function to convert a Gregorian Date into a JD:
Code: Select all
dbadmin=> SELECT SYSDATE::DATE "Gregorian Date", JULIAN_DAY(SYSDATE);
Gregorian Date | JULIAN_DAY
----------------+------------
2013-03-22 | 2456374
(1 row)
In Oracle, it’s very easy to do so using the TO_DATE function:
Code: Select all
SQL> SELECT to_date(2456374, 'J') "Gregorian" FROM dual;
Gregorian
---------
22-MAR-13
We can do something like this:
Code: Select all
dbadmin=> SELECT TIMESTAMPADD ( 'd', 2456374 - 2415021, '01/01/1900')::DATE "Gregorian";
Gregorian
------------
2013-03-22
(1 row)
- TIMESTAMPADD ( 'd', :JULIAN_DATE - 2415021, '01/01/1900')::DATE
Have fun!