Convert a Date to Julian Day in PostgreSQL

In PostgreSQL, you can convert a given date to its Julian day representation by using the to_char() function.

To do this, pass the date as the first argument, and 'J' as the second.

Example 1

Here’s an example to demonstrate.

SELECT to_char(current_timestamp, 'J');

Result:

Here, 2459008 is the Julian day.

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.

PostgreSQL actually uses Julian dates internally for all date/time calculations anyway.

Example 2

Here’s another example that uses a fixed date value.

SELECT to_char('2020-12-16'::date, 'J');

Result: