Converting to a Julian Date and Back Again!

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1775
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Converting to a Julian Date and Back Again!

Post by JimKnicely » 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:

Code: Select all

dbadmin=> SELECT SYSDATE::DATE "Gregorian Date", JULIAN_DAY(SYSDATE);
 Gregorian Date | JULIAN_DAY
----------------+------------
 2013-03-22     |    2456374
(1 row)
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:

Code: Select all

SQL> SELECT to_date(2456374, 'J') "Gregorian" FROM dual;

Gregorian
---------
22-MAR-13
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:

Code: Select all

dbadmin=> SELECT TIMESTAMPADD ( 'd', 2456374 - 2415021, '01/01/1900')::DATE "Gregorian";
 Gregorian
------------
 2013-03-22
(1 row)
In general, to convert a Julian Date into a Gregorian Date, we’d use syntax similar to the following in Vertica:
  • TIMESTAMPADD ( 'd', :JULIAN_DATE - 2415021, '01/01/1900')::DATE
Fun Fact: Internally, Vertica uses Julian Dates for all date time calculations!

Have fun!
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”