Small. Fast. Reliable.
Choose any three.

SQLite Requirement Matrix Details
lang_datefunc.html

Index Summary Markup Original


R-44334-22485-53037-21261-43718-31016-47117-27643 tcl slt th3 src

date(time-value, modifier, modifier, ...)

th3/req1/date01.test:10

/* IMP: R-44334-22485 */
# EVIDENCE-OF: R-44334-22485 date(time-value, modifier, modifier, ...)

R-62773-63407-48411-60588-32030-64322-40065-55617 tcl slt th3 src

time(time-value, modifier, modifier, ...)

th3/req1/date01.test:34

/* IMP: R-62773-63407 */
# EVIDENCE-OF: R-62773-63407 time(time-value, modifier, modifier, ...)

R-42245-25582-08929-62299-62377-14335-00721-06605 tcl slt th3 src

datetime(time-value, modifier, modifier, ...)

th3/req1/date01.test:52

/* IMP: R-42245-25582 */
# EVIDENCE-OF: R-42245-25582 datetime(time-value, modifier, modifier,
# ...)

R-11990-19596-61905-61628-16888-24987-31493-40559 tcl slt th3 src

julianday(time-value, modifier, modifier, ...)

th3/req1/date01.test:86

/* IMP: R-11990-19596 */
# EVIDENCE-OF: R-11990-19596 julianday(time-value, modifier, modifier,
# ...)

R-49482-28255-11165-16969-19865-58468-07547-43545 tcl slt th3 src

strftime(format, time-value, modifier, modifier, ...)

th3/req1/date01.test:109

/* IMP: R-49482-28255 */
# EVIDENCE-OF: R-49482-28255 strftime(format, time-value, modifier,
# modifier, ...)

R-14480-10586-37418-24498-17324-51005-09457-09815 tcl slt th3 src

All five date and time functions take a time value as an argument.

th3/req1/date01.test:12

/* IMP: R-14480-10586 */
# EVIDENCE-OF: R-14480-10586 All five date and time functions take a
# time value as an argument.

R-20765-15127-44657-52295-11493-39499-43241-33689 tcl slt th3 src

The time value is followed by zero or more modifiers.

th3/req1/date01.test:15

/* IMP: R-20765-15127 */
# EVIDENCE-OF: R-20765-15127 The time value is followed by zero or more
# modifiers.

R-55595-13513-43287-07115-18907-23748-32560-50080 tcl slt th3 src

The strftime() function also takes a format string as its first argument.

th3/req1/date01.test:112

/* IMP: R-55595-13513 */
# EVIDENCE-OF: R-55595-13513 The strftime() function also takes a format
# string as its first argument.

R-62098-31964-50805-21241-10330-01586-45989-63464 tcl slt th3 src

The date() function returns the date in this format: YYYY-MM-DD.

th3/req1/date01.test:18

/* IMP: R-62098-31964 */
# EVIDENCE-OF: R-62098-31964 The date() function returns the date in
# this format: YYYY-MM-DD.

R-28740-04940-19074-59456-16824-11848-19165-57032 tcl slt th3 src

The time() function returns the time as HH:MM:SS.

th3/req1/date01.test:36

/* IMP: R-28740-04940 */
# EVIDENCE-OF: R-28740-04940 The time() function returns the time as
# HH:MM:SS.

R-54780-55967-39790-26433-61749-36346-50867-30615 tcl slt th3 src

The datetime() function returns "YYYY-MM-DD HH:MM:SS".

th3/req1/date01.test:54

/* IMP: R-54780-55967 */
# EVIDENCE-OF: R-54780-55967 The datetime() function returns "YYYY-MM-DD
# HH:MM:SS".

R-50991-36730-61115-19569-38902-10025-13040-27195 tcl slt th3 src

The julianday() function returns the Julian day - the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar).

th3/req1/date01.test:88

/* IMP: R-50991-36730 */
# EVIDENCE-OF: R-50991-36730 The julianday() function returns the Julian
# day - the number of days since noon in Greenwich on November 24, 4714
# B.C. (Proleptic Gregorian calendar).

R-06497-44374-00376-59592-23814-22462-41106-13473 tcl slt th3 src

The strftime() routine returns the date formatted according to the format string specified as the first argument.

th3/req1/date01.test:129

/* IMP: R-06497-44374 */
# EVIDENCE-OF: R-06497-44374 The strftime() routine returns the date
# formatted according to the format string specified as the first
# argument.

R-41671-45444-43968-05404-57044-18687-11501-21021 tcl slt th3 src

The format string supports the most common substitutions found in the strftime() function from the standard C library plus two new substitutions, %f and %J.

th3/req1/date01.test:133

/* IMP: R-41671-45444 */
# EVIDENCE-OF: R-41671-45444 The format string supports the most common
# substitutions found in the strftime() function from the standard C
# library plus two new substitutions, %f and %J.

R-52944-64228-64118-58952-09671-12918-63425-40021 tcl slt th3 src

The following is a complete list of valid strftime() substitutions:

%d day of month: 00
%f fractional seconds: SS.SSS
%H hour: 00-24
%j day of year: 001-366
%J Julian day number
%m month: 01-12
%M minute: 00-59
%s seconds since 1970-01-01
%S seconds: 00-59
%w day of week 0-6 with Sunday==0
%W week of year: 00-53
%Y year: 0000-9999
%% %

th3/req1/date01.test:137

/* IMP: R-52944-64228 */
# EVIDENCE-OF: R-52944-64228 The following is a complete list of valid
# strftime() substitutions: %d day of month: 00 %f fractional seconds:
# SS.SSS %H hour: 00-24 %j day of year: 001-366 %J Julian day number %m
# month: 01-12 %M minute: 00-59 %s seconds since 1970-01-01 %S seconds:
# 00-59 %w day of week 0-6 with Sunday==0 %W week of year: 00-53 %Y
# year: 0000-9999 %% %

R-17644-24322-63248-40281-26514-10793-04355-46693 tcl slt th3 src

Notice that all other date and time functions can be expressed in terms of strftime():

FunctionEquivalent strftime()
date(...) strftime('%Y-%m-%d', ...)
time(...) strftime('%H:%M:%S', ...)
datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...) strftime('%J', ...)

th3/req1/date01.test:202

/* IMP: R-17644-24322 */
# EVIDENCE-OF: R-17644-24322 Notice that all other date and time
# functions can be expressed in terms of strftime(): FunctionEquivalent
# strftime() date(...) strftime('%Y-%m-%d', ...) time(...)
# strftime('%H:%M:%S', ...) datetime(...) strftime('%Y-%m-%d %H:%M:%S',
# ...) julianday(...) strftime('%J', ...)

R-42555-53392-53053-11662-61855-46471-62332-42985 tcl slt th3 src

A time value can be in any of the following formats shown below. The value is usually a string, though it can be an integer or floating point number in the case of format 12.

  1. YYYY-MM-DD
  2. YYYY-MM-DD HH:MM
  3. YYYY-MM-DD HH:MM:SS
  4. YYYY-MM-DD HH:MM:SS.SSS
  5. YYYY-MM-DDTHH:MM
  6. YYYY-MM-DDTHH:MM:SS
  7. YYYY-MM-DDTHH:MM:SS.SSS
  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now
  12. DDDDDDDDDD

th3/req1/date01.test:225

/* IMP: R-42555-53392 */
# EVIDENCE-OF: R-42555-53392 A time value can be in any of the following
# formats shown below. The value is usually a string, though it can be
# an integer or floating point number in the case of format 12.
# 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 DDDDDDDDDD

R-24420-43338-21659-30217-32361-58749-59788-18529 tcl slt th3 src

In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by ISO-8601.

th3/req1/date01.test:232

/* IMP: R-24420-43338 */
# EVIDENCE-OF: R-24420-43338 In formats 5 through 7, the "T" is a
# literal character separating the date and the time, as required by
# ISO-8601.

R-02526-60953-53079-20441-57017-47857-19506-30877 tcl slt th3 src

Formats 8 through 10 that specify only a time assume a date of 2000-01-01.

th3/req1/date01.test:236

/* IMP: R-02526-60953 */
# EVIDENCE-OF: R-02526-60953 Formats 8 through 10 that specify only a
# time assume a date of 2000-01-01.

R-34818-13664-64271-58157-25090-18668-59799-37546 tcl slt th3 src

The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call.

tcl/date.test:544   th3/cov1/date4.test:14

/* IMP: R-34818-13664 */
# EVIDENCE-OF: R-34818-13664 The 'now' argument to date and time
# functions always returns exactly the same value for multiple
# invocations within the same sqlite3_step() call.

R-44931-31853-26224-06306-44467-24966-10617-31779 tcl slt th3 src

Universal Coordinated Time (UTC) is used.

th3/req1/date01.test:598

/* IMP: R-44931-31853 */
# EVIDENCE-OF: R-44931-31853 Universal Coordinated Time (UTC) is used.

R-11474-58635-26896-45851-63902-38678-33536-05238 tcl slt th3 src

Format 12 is the Julian day number expressed as an integer or floating point value.

th3/req1/date01.test:239

/* IMP: R-11474-58635 */
# EVIDENCE-OF: R-11474-58635 Format 12 is the Julian day number
# expressed as an integer or floating point value.

R-03263-59522-08977-47312-04173-07916-12369-56758 tcl slt th3 src

In formats 4, 7, and 10, the fractional seconds value SS.SSS can have one or more digits following the decimal point. Exactly three digits are shown in the examples because only the first three digits are significant to the result, but the input string can have fewer or more than three digits and the date/time functions will still operate correctly.

th3/req1/date01.test:280

/* IMP: R-03263-59522 */
# EVIDENCE-OF: R-03263-59522 In formats 4, 7, and 10, the fractional
# seconds value SS.SSS can have one or more digits following the decimal
# point. Exactly three digits are shown in the examples because only the
# first three digits are significant to the result, but the input string
# can have fewer or more than three digits and the date/time functions
# will still operate correctly.

R-09644-54524-34605-02045-19274-34889-27316-65483 tcl slt th3 src

The time value can be followed by zero or more modifiers that alter date and/or time.

th3/req1/date01.test:334

/* IMP: R-09644-54524 */
# EVIDENCE-OF: R-09644-54524 The time value can be followed by zero or
# more modifiers that alter date and/or time.

R-06630-59185-25894-35312-55008-29354-15372-24847 tcl slt th3 src

Each modifier is a transformation that is applied to the time value to its left.

th3/req1/date01.test:585

/* IMP: R-06630-59185 */
# EVIDENCE-OF: R-06630-59185 Each modifier is a transformation that is
# applied to the time value to its left.

R-45424-59143-42887-56356-41413-49128-47927-21331 tcl slt th3 src

Modifiers are applied from left to right; order is important.

th3/req1/date01.test:588

/* IMP: R-45424-59143 */
# EVIDENCE-OF: R-45424-59143 Modifiers are applied from left to right;
# order is important.

R-37051-49391-21174-04435-43019-60258-26390-24659 tcl slt th3 src

The available modifiers are as follows.

  1. NNN days
  2. NNN hours
  3. NNN minutes
  4. NNN.NNNN seconds
  5. NNN months
  6. NNN years
  7. start of month
  8. start of year
  9. start of day
  10. weekday N
  11. unixepoch
  12. localtime
  13. utc

th3/req1/date01.test:337

/* IMP: R-37051-49391 */
# EVIDENCE-OF: R-37051-49391 The available modifiers are as follows. NNN
# days NNN hours NNN minutes NNN.NNNN seconds NNN months NNN years start
# of month start of year start of day weekday N unixepoch localtime utc

R-54293-07495-13048-34845-43688-00379-35800-25394 tcl slt th3 src

The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the arguments to the left.

th3/req1/date01.test:341   th3/req1/date01.test:57

/* IMP: R-54293-07495 */
# EVIDENCE-OF: R-54293-07495 The first six modifiers (1 through 6)
# simply add the specified amount of time to the date and time specified
# by the arguments to the left.

R-47864-57952-52818-65192-57929-18923-19319-26072 tcl slt th3 src

The 's' character at the end of the modifier names is optional.

th3/req1/date01.test:414

/* IMP: R-47864-57952 */
# EVIDENCE-OF: R-47864-57952 The 's' character at the end of the
# modifier names is optional.

R-62792-33996-11773-03176-05046-17928-37966-25264 tcl slt th3 src

Note that "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result.

th3/req1/date01.test:406

/* IMP: R-62792-33996 */
# EVIDENCE-OF: R-62792-33996 Note that "±NNN months" works by
# rendering the original date into the YYYY-MM-DD format, adding the
# ±NNN to the MM month value, then normalizing the result.

R-33841-62945-13926-63288-41300-28246-26461-11874 tcl slt th3 src

Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01.

th3/req1/date01.test:410

/* IMP: R-33841-62945 */
# EVIDENCE-OF: R-33841-62945 Thus, for example, the data 2001-03-31
# modified by '+1 month' initially yields 2001-04-31, but April only has
# 30 days so the date is normalized to 2001-05-01.

R-17537-00183-53931-12298-10408-12793-18687-03447 tcl slt th3 src

A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.

th3/req1/date01.test:424

/* IMP: R-17537-00183 */
# EVIDENCE-OF: R-17537-00183 A similar effect occurs when the original
# date is February 29 of a leapyear and the modifier is ±N
# years where N is not a multiple of four.

R-47125-14019-06302-35267-43761-08118-47718-40685 tcl slt th3 src

The "start of" modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.

th3/req1/date01.test:435

/* IMP: R-47125-14019 */
# EVIDENCE-OF: R-47125-14019 The "start of" modifiers (7 through 9)
# shift the date backwards to the beginning of the current month, year
# or day.

R-59400-47238-48120-20066-59137-27524-23883-51047 tcl slt th3 src

The "weekday" modifier advances the date forward, if necessary, to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.

th3/req1/date01.test:449

/* IMP: R-59400-47238 */
# EVIDENCE-OF: R-59400-47238 The "weekday" modifier advances the date
# forward, if necessary, to the next date where the weekday number is N.
# Sunday is 0, Monday is 1, and so forth.

R-59177-30964-27370-56053-61429-49243-22831-00049 tcl slt th3 src

If the date is already on the desired weekday, the "weekday" modifier leaves the date unchanged.

th3/req1/date01.test:453

/* IMP: R-59177-30964 */
# EVIDENCE-OF: R-59177-30964 If the date is already on the desired
# weekday, the "weekday" modifier leaves the date unchanged.

R-49255-55373-50105-35704-52398-37600-44211-44389 tcl slt th3 src

The "unixepoch" modifier (11) only works if it immediately follows a time value in the DDDDDDDDDD format.

th3/req1/date01.test:487

/* IMP: R-49255-55373 */
# EVIDENCE-OF: R-49255-55373 The "unixepoch" modifier (11) only works if
# it immediately follows a time value in the DDDDDDDDDD format.

R-23075-39245-36315-44421-23373-63820-24651-07544 tcl slt th3 src

This modifier causes the DDDDDDDDDD to be interpreted not as a Julian day number as it normally would be, but as Unix Time - the number of seconds since 1970.

th3/req1/date01.test:490

/* IMP: R-23075-39245 */
# EVIDENCE-OF: R-23075-39245 This modifier causes the DDDDDDDDDD to be
# interpreted not as a Julian day number as it normally would be, but as
# Unix Time - the number of seconds since 1970.

R-55478-19008-07990-22880-24783-58740-20121-48079 tcl slt th3 src

The "localtime" modifier (12) assumes the time value to its left is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime.

th3/req1/date01.test:498

/* IMP: R-55478-19008 */
# EVIDENCE-OF: R-55478-19008 The "localtime" modifier (12) assumes the
# time value to its left is in Universal Coordinated Time (UTC) and
# adjusts that time value so that it is in localtime.

R-43634-32029-21276-39122-52064-20443-35317-63823 tcl slt th3 src

The "utc" modifier is the opposite of "localtime". "utc" assumes that the time value to its left is in the local timezone and adjusts that time value to be in UTC.

th3/req1/date01.test:502

/* IMP: R-43634-32029 */
# EVIDENCE-OF: R-43634-32029 The "utc" modifier is the opposite of
# "localtime". "utc" assumes that the time value to its left is in the
# local timezone and adjusts that time value to be in UTC.

R-56249-28265-13438-15861-43462-53554-35799-32319 tcl slt th3 src

Compute the current date.

SELECT date('now');

th3/req1/date01.test:510

/* IMP: R-56249-28265 */
# EVIDENCE-OF: R-56249-28265 Compute the current date. SELECT
# date('now');

R-25583-11663-64337-14085-59741-00588-27469-12431 tcl slt th3 src

Compute the last day of the current month.

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

th3/req1/date01.test:517

/* IMP: R-25583-11663 */
# EVIDENCE-OF: R-25583-11663 Compute the last day of the current month.
# SELECT date('now','start of month','+1 month','-1 day');

R-54577-32145-57305-62671-23422-08381-26358-15334 tcl slt th3 src

Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');

th3/req1/date01.test:524

/* IMP: R-54577-32145 */
# EVIDENCE-OF: R-54577-32145 Compute the date and time given a unix
# timestamp 1092941466. SELECT datetime(1092941466, 'unixepoch');

R-02834-55381-03461-31664-23015-51190-16436-44295 tcl slt th3 src

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

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

th3/req1/date01.test:478

/* IMP: R-02834-55381 */
# EVIDENCE-OF: R-02834-55381 Compute the date and time given a unix
# timestamp 1092941466, and compensate for your local timezone. SELECT
# datetime(1092941466, 'unixepoch', 'localtime');

R-37271-65264-45647-61848-25356-06624-30160-41691 tcl slt th3 src

Compute the current unix timestamp.

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

th3/req1/date01.test:534

/* IMP: R-37271-65264 */
# EVIDENCE-OF: R-37271-65264 Compute the current unix timestamp. SELECT
# strftime('%s','now');

R-00767-44770-16975-15707-27043-13440-51014-32740 tcl slt th3 src

Compute the number of days since the signing of the US Declaration of Independence.

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

th3/req1/date01.test:541

/* IMP: R-00767-44770 */
# EVIDENCE-OF: R-00767-44770 Compute the number of days since the
# signing of the US Declaration of Independence. SELECT julianday('now')
# - julianday('1776-07-04');

R-37810-60222-47752-18821-24220-18386-52831-63855 tcl slt th3 src

Compute the number of seconds since a particular moment in 2004:

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

th3/req1/date01.test:550

/* IMP: R-37810-60222 */
# EVIDENCE-OF: R-37810-60222 Compute the number of seconds since a
# particular moment in 2004: SELECT strftime('%s','now') -
# strftime('%s','2004-01-01 02:34:56');

R-63725-33821-44537-37255-59793-46389-19158-48724 tcl slt th3 src

Compute the date of the first Tuesday in October for the current year.

SELECT date('now','start of year','+9 months','weekday 2');

th3/req1/date01.test:558

/* IMP: R-63725-33821 */
# EVIDENCE-OF: R-63725-33821 Compute the date of the first Tuesday in
# October for the current year. SELECT date('now','start of year','+9
# months','weekday 2');

R-52095-61560-15875-18075-33970-20286-17473-17110 tcl slt th3 src

Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):

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

th3/req1/date01.test:566

/* IMP: R-52095-61560 */
# EVIDENCE-OF: R-52095-61560 Compute the time since the unix epoch in
# seconds (like strftime('%s','now') except includes fractional part):
# SELECT (julianday('now') - 2440587.5)*86400.0;

R-62172-00036-57834-47035-33859-45076-31922-24757 tcl slt th3 src

In this implementation, the standard C library function localtime_r() is used to assist in the calculation of local time.

src/date.c:509

/* IMP: R-62172-00036 */
# EVIDENCE-OF: R-62172-00036 In this implementation, the standard C
# library function localtime_r() is used to assist in the calculation of
# local time.

R-55269-29598-22145-31317-31671-53288-42244-17124 tcl slt th3 src

The localtime_r() C function normally only works for years between 1970 and 2037. For dates outside this range, SQLite attempts to map the year into an equivalent year within this range, do the calculation, then map the year back.

src/date.c:567

/* IMP: R-55269-29598 */
# EVIDENCE-OF: R-55269-29598 The localtime_r() C function normally only
# works for years between 1970 and 2037. For dates outside this range,
# SQLite attempts to map the year into an equivalent year within this
# range, do the calculation, then map the year back.

R-52653-05256-00101-41227-35426-26812-44795-06520 tcl slt th3 src

These functions only work for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5).

th3/req1/date01.test:574

/* IMP: R-52653-05256 */
# EVIDENCE-OF: R-52653-05256 These functions only work for dates between
# 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julian day numbers
# 1721059.5 through 5373484.5).