SQLite julianday() Function

SQLite julianday() converts a supported time-value to a floating-point Julian day number. The value counts fractional days from noon on November 24, 4714 BC (UTC) in the proleptic Gregorian calendar.

Syntax

julianday(time_value [, modifier, ...])

The time-value is optional; if omitted, SQLite uses now. Accepted inputs include ISO-8601 text and Julian day numbers. To interpret a numeric input as a Unix timestamp, use the unixepoch modifier immediately after it:

SELECT julianday(1748528160, 'unixepoch');

You can also use auto to have SQLite interpret a numeric time-value as either a Julian day number or Unix timestamp. auto is ambiguous for Unix timestamps in the first 63 days of 1970, so use unixepoch when the format is known.

Modifiers are applied from left to right. Examples include '+7 days', 'start of month', 'unixepoch', 'auto', and 'localtime'. See the SQLite date and time documentation for the full list and ordering rules.

Calculate elapsed days

Subtract one Julian day value from another to get elapsed days, including a fractional part:

SELECT julianday('2024-03-01') - julianday('2024-02-01') AS elapsed_days;

elapsed_days
------------
29.0

For elapsed seconds, subtract unixepoch() values. If fractional seconds matter, use unixepoch(time_value, 'subsec') (SQLite 3.42.0 or later); the subsec modifier does not change julianday() output. A Julian day difference already includes fractional days. For a human-readable calendar interval that accounts for month and year lengths, use timediff() instead.

Apply a modifier

This example shifts a date-time one day forward:

SELECT julianday('2024-02-29 12:00:00', '+1 day') AS next_day;

next_day
----------------
2460371.0

julianday() returns a numeric REAL value directly. It avoids converting through a text representation such as strftime('%J', ...) when you need to compare or calculate with day counts.