Menu

SQLite Date Time Functions

SQLite has no dedicated date or time storage type. Store date/time values as ISO-8601 text, Julian day numbers, or Unix timestamps; the date and time functions accept ISO-8601 text and Julian day numbers directly. For Unix seconds, add the 'unixepoch' modifier, for example datetime(1748528160, 'unixepoch').

To format a date in SQLite, use strftime() with format codes such as %Y-%m-%d for a calendar date or %Y-%m-%d %H:%M:%S for a timestamp. Supported codes vary by SQLite version; an unsupported code makes strftime() return NULL. See SQLite strftime() format codes.

Choose a function by task:

  • Return a date, time, or combined date and time with date(), time(), or datetime().
  • Format values with strftime(), including year, month, day, ISO week, and Unix timestamp output.
  • Get a human-readable calendar interval with timediff().
  • Calculate numeric elapsed-time differences with julianday() (days) or unixepoch() (seconds).

For precise Unix timestamp conversion, prefer the explicit 'unixepoch' modifier over 'auto' when you know the input format. 'auto' can misread Unix timestamps from the first 63 days of 1970 as Julian day numbers. Date/time modifiers are applied from left to right, so their order can change the result.

  1. date

    The SQLite date() function converts a time value specified by a time value and modifiers to a date string in YYYY-MM-DD format.

  2. datetime

    The SQLite datetime() function converts a time value specified by a time value and modifiers to a datetime string in YYYY-MM-DD HH:MM:SS format.

  3. julianday

    Use SQLite julianday() to convert time-values into fractional day counts, calculate elapsed days, and apply date modifiers.

  4. strftime

    Format SQLite date and time values with strftime() codes for calendar dates, ISO weeks, fractional seconds, Julian days, and Unix timestamps.

  5. time

    The SQLite time() function converts a time value specified by a time value and modifiers to a time string in HH:MM:SS format.

  6. timediff

    Learn how SQLite timediff() returns a calendar-aware modifier and when to use julianday() or unixepoch() for exact elapsed time.

  7. unixepoch

    Use SQLite unixepoch() to convert time-values to Unix seconds, calculate elapsed time, and preserve fractional seconds with subsec.