Small. Fast. Reliable.
Choose any three.

SQLite Requirement Matrix Details
lang_datefunc.html

Index Summary Markup Original

R-64056-51992-26922-35831-30879-15759-20002-65102 tcl slt th3 src

date(timestring, modifier, modifier, ...)

th3/req1/date01.test:10

/* IMP: R-64056-51992 */

# EVIDENCE-OF: R-64056-51992 date(timestring, modifier, modifier, ...)

R-19445-36510-42868-48881-64287-13454-25209-36891 tcl slt th3 src

time(timestring, modifier, modifier, ...)

th3/req1/date01.test:34

/* IMP: R-19445-36510 */

# EVIDENCE-OF: R-19445-36510 time(timestring, modifier, modifier, ...)

R-24844-01074-31075-35616-03763-50098-34519-03364 tcl slt th3 src

datetime(timestring, modifier, modifier, ...)

th3/req1/date01.test:52

/* IMP: R-24844-01074 */

# EVIDENCE-OF: R-24844-01074 datetime(timestring, modifier, modifier,
# ...)

R-31764-59173-05200-42928-33270-18898-11058-33059 tcl slt th3 src

julianday(timestring, modifier, modifier, ...)

th3/req1/date01.test:86

/* IMP: R-31764-59173 */

# EVIDENCE-OF: R-31764-59173 julianday(timestring, modifier, modifier,
# ...)

R-16414-44626-57508-12770-26365-35758-32257-17918 tcl slt th3 src

strftime(format, timestring, modifier, modifier, ...)

th3/req1/date01.test:109

/* IMP: R-16414-44626 */

# EVIDENCE-OF: R-16414-44626 strftime(format, timestring, modifier,
# modifier, ...)

R-64486-07109-38818-39912-16477-45635-40659-20062 tcl slt th3 src

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

th3/req1/date01.test:12

/* IMP: R-64486-07109 */

# EVIDENCE-OF: R-64486-07109 All five date and time functions take a
# time string as an argument.

R-32521-20590-49288-35397-47504-42377-60996-54301 tcl slt th3 src

The time string is followed by zero or more modifiers.

th3/req1/date01.test:15

/* IMP: R-32521-20590 */

# EVIDENCE-OF: R-32521-20590 The time string 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():

Function Equivalent 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-47539-04989-63867-01741-02652-06159-08105-39145 tcl slt th3 src

A time string can be 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
DDDDDDDDDD

th3/req1/date01.test:225

/* IMP: R-47539-04989 */

# EVIDENCE-OF: R-47539-04989 A time string can be 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 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:230

/* 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:234

/* 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:592

/* IMP: R-44931-31853 */

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

R-60389-26911-28107-48751-35058-52596-03462-21420 tcl slt th3 src

Format 12 is the Julian day number expressed as a floating point value.

th3/req1/date01.test:237

/* IMP: R-60389-26911 */

# EVIDENCE-OF: R-60389-26911 Format 12 is the Julian day number
# expressed as a 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:278

/* 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-21230-37464-43575-41237-19163-46428-41996-08800 tcl slt th3 src

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

th3/req1/date01.test:332

/* IMP: R-21230-37464 */

# EVIDENCE-OF: R-21230-37464 The time string 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:579

/* 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:582

/* 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.

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

th3/req1/date01.test:335

/* 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-48672-52715-12548-65010-48286-41309-29558-44843 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 preceding timestring and modifiers.

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

/* IMP: R-48672-52715 */

# EVIDENCE-OF: R-48672-52715 The first six modifiers (1 through 6)
# simply add the specified amount of time to the date and time specified
# by the preceding timestring and modifiers.

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:412

/* 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:404

/* IMP: R-62792-33996 */

# EVIDENCE-OF: R-62792-33996 Note that "&plusmn;NNN months" works by
# rendering the original date into the YYYY-MM-DD format, adding the
# &plusmn;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:408

/* 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:422

/* 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 &plusmn;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:433

/* 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-35040-63004-09413-17967-18828-35998-50185-50384 tcl slt th3 src

The "weekday" modifier advances the date forward to the next date where the weekday number is N.

th3/req1/date01.test:447

/* IMP: R-35040-63004 */

# EVIDENCE-OF: R-35040-63004 The "weekday" modifier advances the date
# forward to the next date where the weekday number is N.

R-35049-22776-26849-33887-63149-61153-21714-11740 tcl slt th3 src

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

th3/req1/date01.test:481

/* IMP: R-35049-22776 */

# EVIDENCE-OF: R-35049-22776 The "unixepoch" modifier (11) only works if
# it immediately follows a timestring 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:484

/* 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-48397-09304-28438-45944-41933-38380-50154-54249 tcl slt th3 src

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

th3/req1/date01.test:492

/* IMP: R-48397-09304 */

# EVIDENCE-OF: R-48397-09304 The "localtime" modifier (12) assumes the
# time string to its left is in Universal Coordinated Time (UTC) and
# adjusts the time string so that it displays localtime.

R-38731-06596-31810-47089-31718-16313-64789-42383 tcl slt th3 src

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

th3/req1/date01.test:496

/* IMP: R-38731-06596 */

# EVIDENCE-OF: R-38731-06596 The "utc" modifier is the opposite of
# "localtime". "utc" assumes that the string to its left is in the local
# timezone and adjusts that string 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:504

/* 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:511

/* 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:518

/* 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:472

/* 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:528

/* 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:535

/* 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:544

/* 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:552

/* 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:560

/* 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:507

/* 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:565

/* 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-55354-05099-56810-41359-25714-34128-15293-12246 tcl slt th3 src

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

th3/req1/date01.test:568

/* IMP: R-55354-05099 */

# EVIDENCE-OF: R-55354-05099 These functions only work for dates between
# 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julidan day numbers
# 1721059.5 through 5373484.5).


%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
%%		%

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

SQLite Requirement Matrix Details lang_datefunc.html

Index Summary Markup Original

SQLite Requirement Matrix Details
lang_datefunc.html