📅

SQLite Date & Time Functions

SQLite

Complete reference for SQLite date and time functions covering date(), time(), datetime(), julianday(), unixepoch(), strftime(), and timediff(). SQLite has no dedicated date type — dates are stored as TEXT (ISO 8601), REAL (Julian day), or INTEGER (Unix epoch). Updated for SQLite 3.52.

7 functions

Jump to function

date time datetime julianday unixepoch strftime timediff

What are SQLite Date & Time Functions?

SQLite date and time functions operate on date/time values stored as TEXT ('YYYY-MM-DD HH:MM:SS'), REAL (Julian day number), or INTEGER (Unix epoch seconds). Unlike PostgreSQL, there is no native DATE or TIMESTAMP column type in SQLite. strftime() is the most versatile SQLite date function, formatting and computing dates in a single call. timediff() (added in SQLite 3.43.0) computes the difference between two datetime values as a human-readable string.

date

SQLite 3.x+→ TEXT

Returns the date portion of a time value as a TEXT string in YYYY-MM-DD format. If no arguments are given, returns the current UTC date. Equivalent to strftime('%F', TIME_VALUE, ...).

Signature

date(TIME_VALUE, MODIFIER, ...)

Parameters

Parameter	Type	Description
TIME_VALUE	TEXT \| REAL \| INTEGER	Optional. A time value as ISO-8601 text (e.g. '2025-06-15'), a Julian day number (REAL), or the special string 'now'. Defaults to 'now' if omitted.
MODIFIER	TEXT	Optional, repeatable. One or more modifiers applied left-to-right, such as '+7 days', '-1 month', 'start of month', 'start of year', 'start of day', 'weekday N', 'localtime', 'utc'.

Examples

Current UTC date — TIME_VALUE defaults to 'now' when omitted

sql

SELECT date();

→'2025-06-15'

Date 30 days from today — useful for expiry or deadline calculations

sql

SELECT date('now', '+30 days');

→'2025-07-15'

Last day of the current month — chain modifiers for compound calculations

sql

SELECT date('now', 'start of month', '+1 month', '-1 day');

→'2025-06-30'

First day of the current year

sql

SELECT date('now', 'start of year');

→'2025-01-01'

Calculate a 90-day window from a stored ISO-8601 date column

sql

SELECT date(created_at, '+90 days') FROM orders WHERE id = 1;

→'2025-09-13'

⚠Anti-Pattern— Storing dates in non-ISO formats like 'DD/MM/YYYY'

Saving dates as '15/06/2025' or '06-15-2025' is a common mistake. SQLite's date(), datetime(), and strftime() only parse ISO-8601 input ('YYYY-MM-DD'), so every date function call on such a column silently returns NULL. Lexicographic sorting also breaks — '09/2025' sorts before '01/2026' incorrectly.

✓ Instead: Always store dates as 'YYYY-MM-DD' TEXT so that SQLite date functions accept them directly and ORDER BY yields correct chronological results.

Internally, date(X, mods...) is exactly strftime('%F', X, mods...). The dedicated function is faster and more readable. Use 'start of month' plus '+1 month' plus '-1 day' to find the last day of any month — a pattern not available via a single modifier.

◆SQLite Note

SQLite has no dedicated DATE column type. Dates are stored as TEXT (ISO-8601), REAL (Julian day numbers), or INTEGER (Unix timestamps) — the column type is merely a hint. This contrasts with PostgreSQL, which has a true DATE type, CURRENT_DATE, and the AT TIME ZONE operator. Always store dates in ISO-8601 format ('YYYY-MM-DD') in TEXT columns so that lexicographic ordering matches chronological ordering.

time

SQLite 3.x+→ TEXT

Returns the time portion of a time value as a TEXT string in HH:MM:SS format (or HH:MM:SS.SSS when the 'subsec' modifier is used). If no arguments are given, returns the current UTC time. Equivalent to strftime('%T', TIME_VALUE, ...).

Signature

time(TIME_VALUE, MODIFIER, ...)

Parameters

Parameter	Type	Description
TIME_VALUE	TEXT \| REAL \| INTEGER	Optional. A time value as ISO-8601 text, a Julian day number, or 'now'. Defaults to 'now' if omitted. Time-only strings like 'HH:MM:SS' assume a date of 2000-01-01.
MODIFIER	TEXT	Optional, repeatable. Modifiers such as '+1 hour', '-30 minutes', 'localtime', 'utc', or 'subsec' to include fractional seconds.

Examples

Current UTC time in HH:MM:SS

sql

SELECT time();

→'14:32:07'

Current local time — 'localtime' adjusts from UTC to the host timezone

sql

SELECT time('now', 'localtime');

→'10:32:07'

Current time with millisecond precision using the 'subsec' modifier

sql

SELECT time('now', 'subsec');

→'14:32:07.421'

Time arithmetic — adding 30 minutes wraps past midnight correctly

sql

SELECT time('2025-06-15 23:45:00', '+30 minutes');

→'00:15:00'

Convert a stored UTC timestamp column to a fixed offset (e.g. UTC-8)

sql

SELECT time(logged_at, '-8 hours') FROM events LIMIT 1;

→'06:32:07'

⚠Anti-Pattern— Ignoring midnight wrap-around in time arithmetic

Developers often use time() alone to add hours to a stored time value expecting a same-day result, then are surprised when '23:00:00' plus '+2 hours' returns '01:00:00'. The time() function wraps correctly at midnight, but any date context is lost, so the result is a time string with no indication that it belongs to the following day.

✓ Instead: Use datetime() instead of time() whenever your arithmetic may cross midnight, so the date advances automatically and the full timestamp remains unambiguous.

The 'subsec' (or 'subsecond') modifier enables millisecond precision in the output: time('now', 'subsec') returns 'HH:MM:SS.SSS'. This modifier can appear as the very first argument, implicitly using 'now' as the time value — so time('subsec') is valid shorthand.

◆SQLite Note

SQLite has no dedicated TIME column type. Times are stored as TEXT. Unlike PostgreSQL — which has TIME, TIMETZ, and INTERVAL types with full arithmetic operators — SQLite time arithmetic is done by applying string modifiers through these functions. There is no native interval arithmetic; use julianday() or unixepoch() subtraction for precise durations.

datetime

SQLite 3.x+→ TEXT

Returns a combined date and time as a TEXT string in YYYY-MM-DD HH:MM:SS format (or YYYY-MM-DD HH:MM:SS.SSS with the 'subsec' modifier). If no arguments are given, returns the current UTC date and time. Equivalent to strftime('%F %T', TIME_VALUE, ...).

Signature

datetime(TIME_VALUE, MODIFIER, ...)

Parameters

Parameter	Type	Description
TIME_VALUE	TEXT \| REAL \| INTEGER	Optional. A time value as ISO-8601 text, a Julian day number, a Unix timestamp (with 'unixepoch' modifier), or 'now'. Defaults to 'now' if omitted.
MODIFIER	TEXT	Optional, repeatable. Modifiers such as 'unixepoch', '+1 day', '-3 hours', 'start of day', 'localtime', 'utc', or 'subsec'.

Examples

Current UTC date and time — no arguments defaults to 'now'

sql

SELECT datetime();

→'2025-06-15 14:32:07'

Convert a Unix timestamp integer to a human-readable datetime

sql

SELECT datetime(1748528160, 'unixepoch');

→'2025-05-29 14:16:00'

Convert Unix timestamp to local time — apply 'localtime' after 'unixepoch'

sql

SELECT datetime(1748528160, 'unixepoch', 'localtime');

→'2025-05-29 10:16:00'

Compute 6 PM today by snapping to start of day then adding 18 hours

sql

SELECT datetime('now', 'start of day', '+18 hours');

→'2025-06-15 18:00:00'

Current datetime with millisecond precision

sql

SELECT datetime('now', 'subsec');

→'2025-06-15 14:32:07.421'

⚠Anti-Pattern— Comparing datetime strings with > or < when formats are inconsistent

Using WHERE created_at > '2025-06-01' only works reliably because ISO-8601 strings sort lexicographically the same as chronologically. If any rows were stored with a different format — such as 'June 1, 2025' or '01-06-2025' — the comparison silently produces wrong results without any error. Even mixing datetime strings with and without a time component (e.g. '2025-06-01' vs '2025-06-01 00:00:00') can cause subtle ordering bugs.

✓ Instead: Enforce a single consistent ISO-8601 format ('YYYY-MM-DD HH:MM:SS') at insert time using a CHECK constraint or application validation, so string comparison always equals chronological order.

Storing datetime values as 'YYYY-MM-DD HH:MM:SS' TEXT means ORDER BY and comparisons work correctly without any conversion, because ISO-8601 strings sort lexicographically in chronological order. Avoid locale-specific formats like 'MM/DD/YYYY' — they break both sorting and date function inputs.

◆SQLite Note

SQLite has no TIMESTAMP or DATETIME column type — the type affinity is for storage hints only. Values stored as DATETIME TEXT are still just strings. This is a fundamental difference from PostgreSQL, which has timestamptz (timestamp with time zone) that stores UTC and performs automatic timezone conversion. In SQLite, timezone handling is entirely manual using the 'localtime' and 'utc' modifiers.

julianday

SQLite 3.x+→ REAL

Returns the Julian day number as a REAL value — the fractional number of days since noon in Greenwich on November 24, 4714 B.C. (proleptic Gregorian calendar). Useful for precise date arithmetic since subtraction of two Julian day numbers gives the exact number of days between two dates.

Signature

julianday(TIME_VALUE, MODIFIER, ...)

Parameters

Parameter	Type	Description
TIME_VALUE	TEXT \| REAL \| INTEGER	Optional. A time value as ISO-8601 text, an existing Julian day number, a Unix timestamp (with 'unixepoch' modifier), or 'now'. Defaults to 'now' if omitted.
MODIFIER	TEXT	Optional, repeatable. Modifiers such as '+7 days', '-1 month', 'start of month', 'unixepoch', 'localtime', or 'utc'.

Examples

Current Julian day number — a REAL value, not TEXT

sql

SELECT julianday('now');

→2460825.09444444

Exact number of days since US Declaration of Independence — simple subtraction works

sql

SELECT julianday('now') - julianday('1776-07-04');

→91025.09444444

Number of whole days since a row was created — CAST to INTEGER truncates fractional days

sql

SELECT CAST(julianday('now') - julianday(created_at) AS INTEGER) FROM users WHERE id = 1;

→45

Convert current time to Unix timestamp manually: 2440587.5 is the Julian day of 1970-01-01

sql

SELECT (julianday('now') - 2440587.5) * 86400.0;

→1748528160.0

Number of days remaining in a year after Jan 1 — exact to the day

sql

SELECT julianday('2025-12-31') - julianday('2025-01-01');

→364.0

⚠Anti-Pattern— Dividing julianday differences to calculate months or years

It is tempting to compute the number of months between two dates by dividing a julianday difference by 30, or years by dividing by 365. Both produce wrong answers because months range from 28 to 31 days and years can be 365 or 366 days. For example, julianday('2024-03-01') - julianday('2024-02-01') is 29 (leap year), not 30.

✓ Instead: Use strftime('%Y', date_b) - strftime('%Y', date_a) for whole-year counts, or timediff() to get a calendar-aware breakdown of years, months, and days.

julianday(B) - julianday(A) always gives the precise number of days (including fractional days if times differ) between two dates, correctly handling month-length differences, leap years, and DST. This is preferable to timediff() when you need an exact numeric count rather than a human-readable string. The constant 2440587.5 is the Julian day for 1970-01-01 00:00:00 UTC — useful for manual Unix timestamp conversion.

◆SQLite Note

julianday() returns a REAL (floating-point) number, not a string. PostgreSQL has no direct equivalent — the closest is EXTRACT(EPOCH FROM ...) or arithmetic with DATE types. Unlike PostgreSQL's interval arithmetic (e.g. age(timestamp, timestamp)), SQLite date math is done by subtracting two numeric julianday() calls. The fractional part of the Julian day represents the time: 0.5 = midnight, 0.0 = noon.

unixepoch

SQLite 3.38.0+ (released 2022-02-22). Prior versions must use CAST(strftime('%s', ...) AS INT) instead.→ INTEGER

Returns the number of seconds since 1970-01-01 00:00:00 UTC (Unix timestamp) as an INTEGER. With the 'subsec' modifier, returns a REAL with fractional seconds (millisecond precision). Equivalent to CAST(strftime('%s', TIME_VALUE, ...) AS INT). If no arguments are given, returns the current Unix timestamp.

Signature

unixepoch(TIME_VALUE, MODIFIER, ...)

Parameters

Parameter	Type	Description
TIME_VALUE	TEXT \| REAL \| INTEGER	Optional. A time value as ISO-8601 text, a Julian day number, an existing Unix timestamp integer (must be followed by 'unixepoch' modifier to be interpreted as such), or 'now'. Defaults to 'now' if omitted.
MODIFIER	TEXT	Optional, repeatable. Key modifiers: 'unixepoch' (treat the preceding numeric value as a Unix timestamp, not a Julian day), 'subsec' (return REAL with fractional seconds), 'localtime', 'utc', '+N days', '-N hours'.

Examples

Current Unix timestamp as an INTEGER — equivalent to strftime('%s')

sql

SELECT unixepoch();

→1748528160

Current Unix timestamp with millisecond precision — 'subsec' as first arg implies 'now'

sql

SELECT unixepoch('subsec');

→1748528160.421

Seconds elapsed since a specific date — exact integer arithmetic

sql

SELECT unixepoch() - unixepoch('2024-01-01 00:00:00');

→47088160

Round-trip: get Unix timestamp then convert back to datetime — useful for verification

sql

SELECT datetime(unixepoch(), 'unixepoch');

→'2025-05-29 14:16:00'

Duration of a session in seconds by subtracting two stored ISO-8601 datetime columns

sql

SELECT unixepoch(event_time) - unixepoch(start_time) FROM sessions WHERE id = 1;

→3600

⚠Anti-Pattern— Storing Unix timestamps as REAL instead of INTEGER

Declaring a timestamp column as REAL and storing values like 1748528160.0 causes subtle precision problems. Floating-point representation of large integers loses precision beyond ~15 significant digits, and subsecond values such as 1748528160.421 cannot be stored exactly in IEEE 754 double precision. This silently corrupts subsecond timestamps and makes exact equality comparisons unreliable.

✓ Instead: Store Unix timestamps as INTEGER (whole seconds) or, if subsecond precision is required, use INTEGER milliseconds; avoid REAL for timestamp storage.

Before SQLite 3.38.0, developers used the workaround CAST(strftime('%s', ...) AS INT). The dedicated unixepoch() function is cleaner, returns a proper INTEGER type, and supports 'subsec' for millisecond precision. When comparing dates stored as Unix integers in a column, use unixepoch('now') directly in WHERE clauses: WHERE expires_at < unixepoch().

◆SQLite Note

SQLite stores Unix timestamps as plain INTEGER values — there is no timestamptz type like PostgreSQL. PostgreSQL uses EXTRACT(EPOCH FROM NOW()) or to_unix_timestamp() for similar results, and has full timezone-aware types. In SQLite, the 'unixepoch' modifier must immediately follow a numeric time-value to interpret it as a Unix timestamp rather than a Julian day number. Without this modifier, a bare integer like 1748528160 would be misread as a Julian day.

strftime

SQLite 3.x+. Full set of directives shown above available from 3.46.0 (2024-05-23). Earlier versions may not support all directives (unsupported ones return NULL).→ TEXT

Formats a date/time value according to a format string, returning TEXT. It is the most general date/time function — all other date/time functions (date, time, datetime, julianday, unixepoch) are special cases of strftime(). Supports a rich set of % directives including %Y, %m, %d, %H, %M, %S, %s, %j, %w, and many more.

Signature

strftime(FORMAT, TIME_VALUE, MODIFIER, ...)

Parameters

Parameter	Type	Description
FORMAT	TEXT	A format string using % directives: %Y (4-digit year), %m (month 01-12), %d (day 01-31), %H (hour 00-24), %M (minute 00-59), %S (seconds 00-59), %s (Unix timestamp seconds), %j (day of year 001-366), %w (weekday 0=Sunday), %W (week of year, Mon start), %f (fractional seconds SS.SSS), %F (ISO date YYYY-MM-DD), %T (ISO time HH:MM:SS), %J (Julian day number), %% (literal %).
TIME_VALUE	TEXT \| REAL \| INTEGER	Optional. A time value in any supported format: ISO-8601 text, Julian day number, Unix timestamp (with 'unixepoch' modifier), or 'now'. Defaults to 'now' if omitted.
MODIFIER	TEXT	Optional, repeatable. Applied left-to-right: '+N days', '-N hours', '+N months', '+N years', 'start of month', 'start of year', 'start of day', 'weekday N', 'unixepoch', 'julianday', 'auto', 'localtime', 'utc', 'subsec'.

Examples

Format current date as ISO date — same result as date()

sql

SELECT strftime('%Y-%m-%d', 'now');

→'2025-06-15'

European date format with local time — strftime is the only way to produce custom formats

sql

SELECT strftime('%d/%m/%Y %H:%M', 'now', 'localtime');

→'15/06/2025 10:32'

ISO week number string — useful for weekly grouping in reports

sql

SELECT strftime('%Y', 'now') || '-W' || strftime('%W', 'now');

→'2025-W24'

Unix timestamp as TEXT via %s — note it returns TEXT, unlike unixepoch() which returns INTEGER

sql

SELECT strftime('%s', 'now');

→'1748528160'

Group records by year-month — a very common reporting pattern in SQLite

sql

SELECT strftime('%Y-%m', created_at) AS month, COUNT(*) FROM orders GROUP BY month;

→month='2025-06', count=42

⚠Anti-Pattern— Using strftime() on an indexed column in a WHERE clause

Writing WHERE strftime('%Y-%m-%d', created_at) = '2025-06-15' forces SQLite to call strftime() on every row in the table, making a full table scan unavoidable even if created_at has an index. The index is bypassed because the query is filtering on a derived value, not the raw column.

✓ Instead: Store dates in ISO-8601 TEXT and filter with a range comparison: WHERE created_at >= '2025-06-15' AND created_at < '2025-06-16', which allows the index on created_at to be used.

All other date functions are convenience wrappers around strftime(). Key format directives to memorize: %F = YYYY-MM-DD (ISO date), %T = HH:MM:SS (ISO time), %s = Unix timestamp (as TEXT), %J = Julian day (as TEXT), %w = weekday (0=Sunday), %j = day of year. For GROUP BY month queries, strftime('%Y-%m', col) is the canonical SQLite idiom — equivalent to DATE_TRUNC('month', col) in PostgreSQL. Note that strftime always returns TEXT, even for %s and %J.

◆SQLite Note

SQLite's strftime() is modeled on the C standard library strftime() with two additions: %f (fractional seconds) and %J (Julian day number). Unlike PostgreSQL's to_char(timestamp, format) which uses a different format vocabulary (YYYY, MM, DD, HH24, etc.), SQLite uses C-style % directives. There is no SQLite equivalent of PostgreSQL's to_timestamp(text, format) — parsing custom format strings back into dates is not natively supported; you must store dates in ISO-8601 format for the date functions to accept them.

timediff

SQLite 3.43.0+ (released 2023-08-24). Not available in earlier versions.→ TEXT

Returns a human-readable string describing how much time must be added to TIME_VALUE_B to reach TIME_VALUE_A. The result format is (+|-)YYYY-MM-DD HH:MM:SS.SSS and is also a valid modifier for other date/time functions. The invariant datetime(A) = datetime(B, timediff(A, B)) always holds.

Signature

timediff(TIME_VALUE_A, TIME_VALUE_B)

Parameters

Parameter	Type	Description
TIME_VALUE_A	TEXT \| REAL	The target time value. Must be ISO-8601 text or a Julian day number. timediff() does not accept modifiers, so Unix timestamps and the 'unixepoch' modifier are not supported.
TIME_VALUE_B	TEXT \| REAL	The reference (base) time value. The result is the signed difference A minus B. Must be ISO-8601 text or a Julian day number.

Examples

How old Abraham Lincoln would be if still alive — years, months, days, and time

sql

SELECT timediff('now', '1809-02-12');

→'+0216-03-23 14:32:07.000'

Time remaining until end of year — a positive result means A is after B

sql

SELECT timediff('2025-12-31', 'now');

→'+0000-06-16 09:27:53.000'

One month difference — note: same result as March 15 to April 15 despite different day counts

sql

SELECT timediff('2023-02-15', '2023-03-15');

→'-0000-01-00 00:00:00.000'

timediff result is a valid modifier — use it to shift any date by the same span

sql

SELECT datetime('2025-01-01', timediff('2025-06-15', '2025-01-01'));

→'2025-06-15 00:00:00'

Project duration as a human-readable string from two stored datetime columns

sql

SELECT timediff(end_time, start_time) AS duration FROM projects WHERE id = 1;

→'+0000-02-14 08:30:00.000'

⚠Anti-Pattern— Treating the timediff() result as a numeric value

A common mistake is trying to compare or do arithmetic directly on the timediff() output, for example WHERE timediff('now', created_at) > 30 or CAST(timediff(...) AS INTEGER). The return value is a formatted string like '+0000-02-14 08:30:00.000', not a number, so numeric comparisons produce unexpected results or errors. SQLite will not raise an error — it will silently coerce the string to 0.

✓ Instead: Use unixepoch('now') - unixepoch(created_at) to get a plain integer count of seconds, or julianday() subtraction for a fractional day count, whenever you need a numeric result.

timediff() gives a calendar-aware human-friendly result (like '2 months and 14 days') but the same timediff() can return the same string for spans of different actual day counts — February to March is '-0000-01-00...' and so is March to April, despite being 28 vs 31 days. When you need a precise count of days or seconds, use julianday(B) - julianday(A) or unixepoch(B) - unixepoch(A) instead. timediff() does not accept the 'unixepoch' modifier, so both arguments must be ISO-8601 strings or Julian day numbers.

◆SQLite Note

timediff() is SQLite's closest analog to PostgreSQL's AGE(timestamp, timestamp) function, which also returns a human-readable interval like '2 years 3 mons 14 days'. However, PostgreSQL's INTERVAL is a proper type that supports arithmetic operators (+, -, *) and can be stored in columns. SQLite's timediff() result is just a TEXT string — it can be passed as a modifier to datetime() but cannot be added or multiplied directly. For storing durations, use INTEGER seconds (from unixepoch subtraction) rather than timediff strings.

Related SQLite Categories

Core Functions →Aggregate Functions →