SQLite julianday: Calculate Date Differences

A query comparing two calendar dates needs a numeric difference to show elapsed time. SQLite’s julianday() function converts supported date and time values into day counts that you can subtract.

You’ll see how to calculate the number of days between dates and keep fractional days when times matter.

TL;DR

Subtract julianday() results for elapsed days, including fractional days, or subtract unixepoch() results for whole seconds.

  • Use julianday(end_time) – julianday(start_time) for days.
  • Multiply the result by 24 for hours, or by 86400 for seconds.
  • Use unixepoch() subtraction when seconds are the useful unit.

What Is julianday() in SQLite?

SQLite’s julianday() converts a supported time-value into a numeric Julian day number. The value counts days from a historical reference point at noon in Greenwich, so the fractional part represents a fraction of a day.

The noon reference can seem unusual, but it does not complicate ordinary differences. Both inputs use the same origin, so that shared offset cancels when one result is subtracted from the other. You can think of the returned number as a coordinate on a continuous day scale rather than a calendar label.

For example, the Julian day value for midnight includes a half-day fraction because the count rolls over at noon. Subtracting two such values still gives the elapsed duration between their instants. The returned result is a floating-point number, so decimal digits after the point represent partial days.

Date and time functions accept text values in supported ISO-8601 forms, as well as Julian day numbers. Unix timestamps need an explicit unixepoch modifier when passed as numeric inputs to functions that expect a time-value. A bare number in the date-function argument position follows SQLite’s numeric time-value interpretation, rather than representing seconds from 1970.

That conversion step is why directly subtracting two text values produces a different operation. Text subtraction may coerce values using SQLite’s general numeric rules, rather than interpreting the date and time fields. Applying julianday() first makes the intended date interpretation explicit.

SQLite also provides unixepoch(), which returns seconds from 1970-01-01 UTC. Both functions accept supported text time-values, while a Unix timestamp should be marked with the unixepoch modifier when supplied as input. The SQLite date and time guide covers the wider set of date functions and modifiers.

How to Calculate SQLite Date Differences with julianday()

Convert both endpoints with the same function, then subtract the start from the end. These examples use the SQLite command-line program and fixed time-values so the output stays the same.

Step 1: Subtract two dates to get days

For date-only inputs, each value represents midnight, and subtracting the Julian day numbers returns elapsed whole days.

SELECT julianday('2024-02-29') - julianday('2024-02-28') AS days;
1.0

The result is numeric and uses days. Leap days need no special formula because SQLite interprets each valid date before the subtraction.

Step 2: Keep the fractional part for time differences

When the inputs include times, the difference can be a fraction of a day. Multiply that fraction by 24 to express it in hours.

SELECT (julianday('2024-02-29 12:00:00') - julianday('2024-02-28 00:00:00')) * 24 AS hours;
36.0

I ran the query and got 36.0 hours, which includes the half-day after the calendar date changed. Keep the fractional value when partial days matter.

Step 3: Calculate elapsed seconds

For whole-second differences, unixepoch() returns a value in seconds, so subtracting its results avoids converting from days. If your starting point is a day difference instead, multiply by 86400 to express that duration in seconds. Multiplying the same result by 1440 gives minutes, because a day has 1440 minutes under SQLite’s 86400-second day model.

Choose one unit for a comparison and apply the conversion to the whole difference. Avoid rounding each endpoint before subtracting. For example, rounding two Julian day values separately can discard fractions on both sides, while subtracting first preserves the interval before you decide how to display it.

SELECT unixepoch('2024-02-29') - unixepoch('2024-02-28') AS seconds;
86400

A day contains 86400 seconds in SQLite’s date calculations. For fractional seconds, the SQLite reference documents the subsec modifier for unixepoch().

Step 4: Calculate a difference from table columns

Use the same expression with column names when each row stores a start and end value. The aliases make the output unit clear to query callers.

SELECT started_at,
       finished_at,
       julianday(finished_at) - julianday(started_at) AS elapsed_days,
       (julianday(finished_at) - julianday(started_at)) * 24 AS elapsed_hours
FROM jobs;

This query assumes the columns hold values SQLite’s date functions can interpret. For Unix timestamp columns, pass each value through unixepoch conversion or convert the timestamp with the unixepoch modifier before applying date functions.

The order of subtraction controls the sign. Put the later endpoint first when you want a positive duration. Reversing the endpoints returns a negative duration, which can be useful for signed offsets, but it can surprise code that expects a nonnegative count.

The date-only example counts the elapsed interval between midnights. It does not count both endpoint dates as separate calendar days. For a report that calls a start date and end date inclusive, add one to the result only after confirming that inclusive calendar-day counting is the rule the report needs.

Elapsed hours and calendar hours can differ around daylight-saving changes when local time-values are involved. SQLite’s documentation warns that local-time conversion depends on the platform C library and historical rules. For consistent elapsed-time arithmetic, store and compare UTC timestamps, then convert for display.

SQLite’s internal calculations treat each day as 86400 seconds and do not account for leap seconds. That model fits ordinary application timestamps, but it is not a scientific time scale for measuring intervals across leap-second events.

Floating-point results may display extra decimal digits that are artifacts of binary representation. If a downstream display needs a fixed number of decimal places, round only for presentation. Keep the unrounded value for later arithmetic when the precision matters.

When a time-value is invalid, date functions can return NULL rather than a numeric day count. In a query over a table, one malformed value can therefore produce a NULL difference for that row. Validate inputs at insertion or account for NULL results in the query that consumes the duration.

Finally, a duration in days does not answer every calendar question. Calendar months have different lengths, and dividing elapsed days by 365 skips leap years and birthdays. Use a calendar-specific rule for those questions instead of treating a fractional Julian day difference as a calendar age.

The table example returns a duration for each row, so one query can compare processing times across jobs. Add a WHERE condition if you need rows above a threshold.

Compare the day difference with a bound in days, or compare the hour expression with a bound in hours. Keep the bound in the same unit as the expression.

When you only need to test whether an interval exceeds a threshold, compare the difference directly rather than converting to a display string. A day difference of 0.5 represents twelve elapsed hours. A bound such as 1.0 asks whether the interval reached one full day, independent of timestamp display formatting.

For repeated calculations, an expression based on stored columns can be used in SELECT or a filtering condition. If the query runs over many rows, inspect its query plan and schema before adding an expression index. Date parsing and indexing choices depend on the table and workload, not on the subtraction formula alone.

Date-only inputs return a whole-day difference when the endpoints are midnight values. Timestamp inputs can return a partial day.

To check a two-hour threshold, compare the day difference with 2.0 / 24.0 or compare an hour-converted result with 2.0. Keep the conversion and the threshold in one explicit unit.

When an interval crosses midnight, its calendar dates change even if less than 24 hours pass. A date-only difference measures the distance between midnight points, while timestamp subtraction measures the interval between the supplied instants. Pick the input precision to match the question you are answering.

The unit also matters when the value is stored or sent to application code. Name an alias elapsed_days or elapsed_seconds rather than diff, so a caller can see what the arithmetic returns without tracing every expression.

Edge Cases When Subtracting SQLite Dates

The result is elapsed duration, and integer conversion truncates the fractional part. Check both details before using a difference as a whole-day count or an age.

I tested a span that begins and ends on the same date, with nearly a full day between the times. Casting the difference directly to INTEGER returned 0:

SELECT CAST(julianday('2024-02-28 23:59:00') - julianday('2024-02-28 00:01:00') AS INTEGER) AS whole_days;
0

The underlying elapsed span is 23 hours and 58 minutes, or just under one day. CAST to INTEGER truncates toward zero, so use the uncast fraction for elapsed time. If the task means completed calendar boundaries, define that rule separately instead of assuming a numeric cast expresses it.

A Julian day difference also does not directly give a calendar age in years or months. Dividing by a fixed number of days per year approximates elapsed time, but birthdays and month lengths require calendar-aware logic.

For two time-values to produce a useful result, SQLite must be able to interpret each input. Unsupported or out-of-range inputs return NULL, according to the SQLite date and time function reference. The reference states that supported dates range from year 0000 through 9999 and that its calculations do not include leap seconds.

Choose julianday() when fractional days help express the task. Choose unixepoch() when seconds are the natural unit. For more built-in functions, see the SQLite functions reference.

Conclusion: Choose the Unit Before You Subtract

Subtract julianday() results for elapsed days and retain the fraction when partial days count. Use unixepoch() subtraction for seconds, and avoid integer casts until the meaning of “whole days” is clear.

For related details, read the SQLite date and time guide and the SQLite strftime reference.

FAQ

These answers cover the unit returned by julianday() and how to choose a date-difference expression.

How do I find the number of days between two dates in SQLite?

Subtract one julianday() result from the other, such as julianday(end_date) – julianday(start_date). The result is elapsed days and can include a fraction.

Does SQLite have a DATEDIFF function?

Use SQLite date and time functions such as julianday() or unixepoch() and subtract their numeric results to calculate elapsed time.

How do I get hours between timestamps with julianday()?

Subtract the julianday() values and multiply the result by 24 to convert days to hours.