Converting to a Julian Date and Back Again!
Posted: Fri Mar 22, 2013 4:27 pm
Hi!
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:
But how do we get back to a Gregorian Date given a Julian Date?
In Oracle, it’s very easy to do so using the TO_DATE function:
Unfortunately, the “J” template string doesn’t work in Vertica’s implementation of the TO_DATE function. But there is a work around!
We can do something like this:
In general, to convert a Julian Date into a Gregorian Date, we’d use syntax similar to the following in Vertica:
Have fun!
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-13We 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!