Here are two ways to convert a given Julian day to its date value in PostgreSQL.

The first method directly inputs the Julian day into the date data type.

The second method uses the to_date() function to return the same result.

Julian day is the number of days since Monday, January 1, 4713 BC using the proleptic Julian calendar, which is November 24, 4714 BC, in the proleptic Gregorian calendar.

Direct Input

PostgreSQL actually uses Julian dates internally for all date/time calculations. Therefore, we can use the J prefix along with ::date to use the Julian day as direct input into the date data type.

Here’s an example to demonstrate.

SELECT 'J2459569'::date;

Result:

2021-12-20

Here, the Julian day is 2459569, which represents 2021-12-20.

The to_date() Function

An alternative, slightly less concise, method is to use the to_date() function.

SELECT to_date('2459569', 'J');

Result:

2021-12-20

Same result.