The SQLite julianday()
function returns the number of days since noon in Greenwich on November 24, 4714 B.C. (using the Proleptic Gregorian calendar).
If using the proleptic Julian calendar, this is Monday, January 1, 4713 BC.
Julian day is the continuous count of days since the beginning of the Julian Period. It’s typically used by astronomers, software, etc to calculate elapsed days between two events.
To use this function, you need to provide a time string, plus any (optional) modifiers. A modifier allows you to change the date, such as add a number of days, set it to local time, etc
Syntax
The syntax goes like this:
julianday(timestring, modifier, modifier, ...)
The timestring
argument must be a valid time string.
The modifier
arguments are optional. You can provide one or more modifiers. If you provide a modifier, it must be a valid modifier.
Example
Here’s an example to demonstrate the julianday()
function being used with one argument.
SELECT julianday('now');
Result:
2458968.52391635
The now
time string is converted into the Julian Day.
Add a Modifier
We can modify the previous result by using a modifier. Here’s an example.
SELECT julianday('now', '+3 hours');
Result:
2458968.65149612
Multiple Modifiers
As mentioned, you can add one or more modifiers. Here’s an example of adding another modifier to the previous example.
SELECT julianday('now', '+3 hours', 'localtime');
Result:
2458969.0685371
julianday() vs strftime()
The julianday()
function returns exactly the same result that strftime('%J', ...)
returns. The julianday()
function is just a more convenient way to do it.
SELECT
julianday('now'),
strftime('%J', 'now');
Result:
julianday('now') strftime('%J', 'now') ---------------- --------------------- 2458968.52807836 2458968.528078356
Date Range
As with all SQLite date and time functions, julianday()
only works for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (Julian Day numbers 1721059.5 through 5373484.5).
For dates outside that range, the results are undefined.