Date and time functions

The following table describes the supported date and time SQL functions.

Function Description
date(time-value, modifier, ...) Returns the date in YYYY-MM-DD format.
time(time-value, modifier, ...) Returns the time in HH:MM:SS format.
datetime(time-value, modifier, ...) Returns the date and time in YYYY-MM-DD HH:MM:SS format.
julianday(time-value, modifier, ...) Returns the Julian day number (days since November 24, 4714 B.C.).
unixepoch(time-value, modifier, ...) Returns the Unix timestamp (seconds since 1970-01-01 00:00:00 UTC).
strftime(format, time-value, modifier, ...) Returns a formatted date/time string according to the format specified as the first argument.
timediff(time-value, time-value) Returns a string describing the time difference between two time values.

Time value formats

Time values can be specified in any of the following formats:

  • YYYY-MM-DD
  • YYYY-MM-DD HH:MM
  • YYYY-MM-DD HH:MM:SS
  • YYYY-MM-DD HH:MM:SS.SSS
  • YYYY-MM-DDTHH:MM
  • YYYY-MM-DDTHH:MM:SS
  • YYYY-MM-DDTHH:MM:SS.SSS
  • HH:MM
  • HH:MM:SS
  • HH:MM:SS.SSS
  • now (current date and time)
  • Julian day number as a numeric value

Time values may also include timezone indicators in the form of [+-]HH:MM or Z.

Modifiers

The following modifiers can be used with date and time functions.

Modifier category Examples
Time addition NNN days, NNN hours, NNN minutes, NNN seconds, NNN months, NNN years
Time shifts ±HH:MM, ±HH:MM:SS, ±HH:MM:SS.SSS, ±YYYY-MM-DD, etc.
Anchor points start of month, start of year, start of day
Day adjustment weekday N (where N is 0-6, Sunday is 0)
Format control subsec/subsecond (increases resolution to include milliseconds)
Time zone conversion localtime, utc
Interpretation unixepoch, julianday, auto
Ambiguity resolution ceiling, floor

Format specifiers for strftime()

The following table describes the most commonly used format specifiers for the strftime() function.

Specifier Description Example
%d Day of month: 01-31 05
%e Day of month without leading zero: 1-31 5
%f Fractional seconds: SS.SSS .123
%F ISO 8601 date: YYYY-MM-DD 2025-05-23
%H Hour in 24-hour format: 00-23 14
%I Hour in 12-hour format: 01-12 02
%j Day of year: 001-366 143
%J Julian day number (fractional) 2460453.5
%m Month: 01-12 05
%M Minute: 00-59 30
%p AM or PM PM
%P am or pm pm
%s Seconds since 1970-01-01 1716480600
%S Seconds: 00-59 45
%T ISO 8601 time: HH:MM:SS 14:30:45
%Y Year: 0000-9999 2025