*** DRAFT ***

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

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

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

/* IMP: R-11990-19596 */

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

R-45708-63005-00796-48255-23802-00758-53162-08820 tcl slt th3 src

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

tcl/date3.test:33 th3/req1/date01.test:127

/* IMP: R-45708-63005 */

# EVIDENCE-OF: R-45708-63005 unixepoch(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:153

/* IMP: R-49482-28255 */

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

R-52907-56880-27693-28944-05946-50403-11424-10789 tcl slt th3 src

All six date and time functions take an optional time value as an argument, followed by zero or more modifiers.

th3/req1/date01.test:12

/* IMP: R-52907-56880 */

# EVIDENCE-OF: R-52907-56880 All six date and time functions take an
# optional time value as an argument, 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:156

/* IMP: R-55595-13513 */

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

R-46397-09573-26570-19364-20067-27452-10393-00737 tcl slt th3 src

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

th3/req1/date01.test:16

/* IMP: R-46397-09573 */

# EVIDENCE-OF: R-46397-09573 The date() function returns the date as
# text in this format: YYYY-MM-DD.

R-01722-24158-57472-43965-29775-29032-59147-63158 tcl slt th3 src

The time() function returns the time as text in this format: HH:MM:SS.

th3/req1/date01.test:42

/* IMP: R-01722-24158 */

# EVIDENCE-OF: R-01722-24158 The time() function returns the time as
# text in this format: HH:MM:SS.

R-64048-35950-52196-42061-52178-11098-48520-29989 tcl slt th3 src

The datetime() function returns the date and time as text in their same formats: YYYY-MM-DD HH:MM:SS.

th3/req1/date01.test:64

/* IMP: R-64048-35950 */

# EVIDENCE-OF: R-64048-35950 The datetime() function returns the date
# and time as text in their same formats: YYYY-MM-DD HH:MM:SS.

R-43640-59273-60623-09093-33059-53394-31507-55495 tcl slt th3 src

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

th3/req1/date01.test:102

/* IMP: R-43640-59273 */

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

R-30877-63179-19996-25083-08228-50110-54093-45033 tcl slt th3 src

The unixepoch() function returns a unix timestamp - the number of seconds since 1970-01-01 00:00:00 UTC.

tcl/date3.test:43 th3/req1/date01.test:129

/* IMP: R-30877-63179 */

# EVIDENCE-OF: R-30877-63179 The unixepoch() function returns a unix
# timestamp - the number of seconds since 1970-01-01 00:00:00 UTC.

R-62992-54137-18704-21860-20022-25020-23814-10304 tcl slt th3 src

The unixepoch() always returns an integer, even if the input time-value has millisecond precision.

tcl/date3.test:51 th3/req1/date01.test:143

/* IMP: R-62992-54137 */

# EVIDENCE-OF: R-62992-54137 The unixepoch() always returns an integer,
# even if the input time-value has millisecond precision.

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

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

/* 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-20991-58040-19506-53014-65349-30649-14089-53276 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 (fractional)
%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:186

/* IMP: R-20991-58040 */

# EVIDENCE-OF: R-20991-58040 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
# (fractional) %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-16050-33177-33953-46606-13298-00172-25196-24218 tcl slt th3 src

All other date and time functions can be expressed in terms of strftime():

Function Equivalent (or nearly) strftime()
date(...) strftime('%Y-%m-%d', ...)
time(...) strftime('%H:%M:%S', ...)
datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...) strftime('%J', ...) -- note-1
unixepoch(...) strftime('%s', ...) -- note-1

th3/req1/date01.test:252

/* IMP: R-16050-33177 */

# EVIDENCE-OF: R-16050-33177 All other date and time functions can be
# expressed in terms of strftime(): FunctionEquivalent (or nearly)
# strftime() date(...) strftime('%Y-%m-%d', ...) time(...)
# strftime('%H:%M:%S', ...) datetime(...) strftime('%Y-%m-%d %H:%M:%S',
# ...) julianday(...) strftime('%J', ...) -- note-1 unixepoch(...)
# strftime('%s', ...) -- note-1

R-60391-12216-45718-54905-27899-48976-39746-28204 tcl slt th3 src

The date(), time(), and datetime() functions all return text, and so their strftime() equivalents are exact.

th3/req1/date01.test:259

/* IMP: R-60391-12216 */

# EVIDENCE-OF: R-60391-12216 The date(), time(), and datetime()
# functions all return text, and so their strftime() equivalents are
# exact.

R-26895-39918-47763-14814-48486-12298-50691-17007 tcl slt th3 src

However (note-1) the julianday() and unixepoch() functions return numeric values. Their strftime() equivalents return strings that is the text representation of the corresponding number.

th3/req1/date01.test:279

/* IMP: R-26895-39918 */

# EVIDENCE-OF: R-26895-39918 However (note-1) the julianday() and
# unixepoch() functions return numeric values. Their strftime()
# equivalents return strings that is the text representation of the
# corresponding number.

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.

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

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

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

/* 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-48198-01058-04098-49437-45599-59984-16743-57318 tcl slt th3 src

Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use.

th3/req1/date01.test:618

/* IMP: R-48198-01058 */

# EVIDENCE-OF: R-48198-01058 Format 11, the string 'now', is converted
# into the current date and time as obtained from the xCurrentTime
# method of the sqlite3_vfs object in use.

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:476 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:718

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

/* 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-24981-18755-32291-32034-18721-01348-44250-24952 tcl slt th3 src

Format 12 might also be interpreted as a unix timestamp if it is immediately followed either the 'auto' or 'unixepoch' modifier.

th3/req1/date01.test:638

/* IMP: R-24981-18755 */

# EVIDENCE-OF: R-24981-18755 Format 12 might also be interpreted as a
# unix timestamp if it is immediately followed either the 'auto' or
# 'unixepoch' modifier.

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

/* 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-15238-25185-55921-28860-50781-00203-08112-61248 tcl slt th3 src

The time-value (and all modifiers) may be omitted, in which case a time value of 'now' is assumed.

th3/req1/date01.test:33

/* IMP: R-15238-25185 */

# EVIDENCE-OF: R-15238-25185 The time-value (and all modifiers) may be
# omitted, in which case a time value of 'now' is assumed.

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

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

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

/* IMP: R-45424-59143 */

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

R-09727-49113-52842-04896-61000-44446-44783-09009 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
julianday
auto
localtime
utc

th3/req1/date01.test:410

/* IMP: R-09727-49113 */

# EVIDENCE-OF: R-09727-49113 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 julianday auto
# 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:415 th3/req1/date01.test:67

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

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

/* 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-42893-46431-20784-14098-09161-04646-55397-28010 tcl slt th3 src

Thus, for example, the date 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:484

/* IMP: R-42893-46431 */

# EVIDENCE-OF: R-42893-46431 Thus, for example, the date 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:498

/* 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-17113-64042-22651-43669-37764-16281-15802-46090 tcl slt th3 src

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

th3/req1/date01.test:509

/* IMP: R-17113-64042 */

# EVIDENCE-OF: R-17113-64042 The "start of" modifiers (7 through 9)
# shift the date backwards to the beginning of the subject 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:523

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

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

src/date.c:736 tcl/date3.test:110 th3/cov1/date1.test:538 th3/req1/date01.test:561

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

tcl/date3.test:113 th3/req1/date01.test:564

/* 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-21150-52363-44475-50752-61726-33318-55013-63350 tcl slt th3 src

The "julianday" modifier must immediately follow the initial time-value which must be of the form DDDDDDDDD.

tcl/date3.test:120 th3/req1/date01.test:573

/* IMP: R-21150-52363 */

# EVIDENCE-OF: R-21150-52363 The "julianday" modifier must immediately
# follow the initial time-value which must be of the form DDDDDDDDD.

R-31176-64601-35633-00922-47448-07633-63744-55833 tcl slt th3 src

Any other use of the 'julianday' modifier is an error and causes the function to return NULL.

src/date.c:707 tcl/date3.test:123 th3/req1/date01.test:576

/* IMP: R-31176-64601 */

# EVIDENCE-OF: R-31176-64601 Any other use of the 'julianday' modifier
# is an error and causes the function to return NULL.

R-32483-36353-28579-36241-32872-31249-13035-18067 tcl slt th3 src

The 'julianday' modifier forces the time-value number to be interpreted as a julian-day number.

tcl/date3.test:126 th3/req1/date01.test:579

/* IMP: R-32483-36353 */

# EVIDENCE-OF: R-32483-36353 The 'julianday' modifier forces the
# time-value number to be interpreted as a julian-day number.

R-25859-20124-54216-15977-54945-58471-03885-24371 tcl slt th3 src

The only difference is that adding 'julianday' forces the DDDDDDDDD time-value format, and causes a NULL to be returned if any other time-value format is used.

tcl/date3.test:129 th3/req1/date01.test:582

/* IMP: R-25859-20124 */

# EVIDENCE-OF: R-25859-20124 The only difference is that adding
# 'julianday' forces the DDDDDDDDD time-value format, and causes a NULL
# to be returned if any other time-value format is used.

R-33611-57934-17501-27849-33098-62168-45294-46068 tcl slt th3 src

The "auto" modifier must immediately follow the initial time-value.

src/date.c:680 th3/req1/date01.test:596

/* IMP: R-33611-57934 */

# EVIDENCE-OF: R-33611-57934 The "auto" modifier must immediately follow
# the initial time-value.

R-05412-24332-54065-34231-12787-10202-34287-53186 tcl slt th3 src

If the time-value is numeric (the DDDDDDDDDD format) then the 'auto' modifier causes the time-value to interpreted as either a julian day number or a unix timestamp, depending on its magnitude.

tcl/date3.test:56

/* IMP: R-05412-24332 */

# EVIDENCE-OF: R-05412-24332 If the time-value is numeric (the
# DDDDDDDDDD format) then the 'auto' modifier causes the time-value to
# interpreted as either a julian day number or a unix timestamp,
# depending on its magnitude.

R-56763-40111-20796-18613-30177-15204-60661-20529 tcl slt th3 src

If the value is between 0.0 and 5373484.499999, then it is interpreted as a julian day number (corresponding to dates between -4713-11-24 12:00:00 and 9999-12-31 23:59:59, inclusive).

tcl/date3.test:61

/* IMP: R-56763-40111 */

# EVIDENCE-OF: R-56763-40111 If the value is between 0.0 and
# 5373484.499999, then it is interpreted as a julian day number
# (corresponding to dates between -4713-11-24 12:00:00 and 9999-12-31
# 23:59:59, inclusive).

R-07289-49223-41401-05780-36220-42267-14468-32307 tcl slt th3 src

For numeric values outside of the range of valid julian day numbers, but within the range of -210866760000 to 253402300799, the 'auto' modifier causes the value to be interpreted as a unix timestamp.

tcl/date3.test:66

/* IMP: R-07289-49223 */

# EVIDENCE-OF: R-07289-49223 For numeric values outside of the range of
# valid julian day numbers, but within the range of -210866760000 to
# 253402300799, the 'auto' modifier causes the value to be interpreted
# as a unix timestamp.

R-20795-34947-09071-12465-14308-46505-11000-12464 tcl slt th3 src

Other numeric values are out of range and cause a NULL return.

tcl/date3.test:71

/* IMP: R-20795-34947 */

# EVIDENCE-OF: R-20795-34947 Other numeric values are out of range and
# cause a NULL return.

R-38886-35357-61293-06392-16818-17229-57079-41544 tcl slt th3 src

The 'auto' modifier is a no-op for text time-values.

tcl/date3.test:92

/* IMP: R-38886-35357 */

# EVIDENCE-OF: R-38886-35357 The 'auto' modifier is a no-op for text
# time-values.

R-53132-26856-55284-61256-49129-35649-13500-14879 tcl slt th3 src

The 'auto' modifier can be used to work with date/time values even in cases where it is not known if the julian day number or unix timestamp formats are in use.

tcl/date3.test:97

/* IMP: R-53132-26856 */

# EVIDENCE-OF: R-53132-26856 The 'auto' modifier can be used to work
# with date/time values even in cases where it is not known if the
# julian day number or unix timestamp formats are in use.

R-33431-18865-50841-57619-09103-02697-30472-50548 tcl slt th3 src

Unix timestamps for the first 63 days of 1970 will be interpreted as julian day numbers.

tcl/date3.test:139

/* IMP: R-33431-18865 */

# EVIDENCE-OF: R-33431-18865 Unix timestamps for the first 63 days of
# 1970 will be interpreted as julian day numbers.

R-61302-08786-38970-33411-07137-48522-41365-64379 tcl slt th3 src

The "localtime" modifier (14) 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:604

/* IMP: R-61302-08786 */

# EVIDENCE-OF: R-61302-08786 The "localtime" modifier (14) 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:608

/* 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-03796-40736-43396-40184-40689-11911-14081-05608 tcl slt th3 src

Compute the current date.

SELECT date();

th3/req1/date01.test:616

/* IMP: R-03796-40736 */

# EVIDENCE-OF: R-03796-40736 Compute the current date. SELECT date();

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

/* 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-43458-55246-11527-54416-50784-11125-14685-56164 tcl slt th3 src

Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');
SELECT datetime(1092941466, 'auto'); -- Does not work for early 1970!

th3/req1/date01.test:634

/* IMP: R-43458-55246 */

# EVIDENCE-OF: R-43458-55246 Compute the date and time given a unix
# timestamp 1092941466. SELECT datetime(1092941466, 'unixepoch'); SELECT
# datetime(1092941466, 'auto'); -- Does not work for early 1970!

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

/* 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-41079-08948-19463-36063-08985-63659-05958-53275 tcl slt th3 src

Compute the current unix timestamp.

SELECT unixepoch();
SELECT strftime('%s');

th3/req1/date01.test:653

/* IMP: R-41079-08948 */

# EVIDENCE-OF: R-41079-08948 Compute the current unix timestamp. SELECT
# unixepoch(); SELECT strftime('%s');

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

/* 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-52732-49288-09646-62547-34370-14326-57234-39193 tcl slt th3 src

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

SELECT unixepoch() - unixepoch('2004-01-01 02:34:56');

th3/req1/date01.test:670

/* IMP: R-52732-49288 */

# EVIDENCE-OF: R-52732-49288 Compute the number of seconds since a
# particular moment in 2004: SELECT unixepoch() - unixepoch('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:678

/* 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-55038-56206-39192-62322-52074-12429-49711-21478 tcl slt th3 src

Compute the time since the unix epoch in seconds with millisecond precision:

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

th3/req1/date01.test:686

/* IMP: R-55038-56206 */

# EVIDENCE-OF: R-55038-56206 Compute the time since the unix epoch in
# seconds with millisecond precision: 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:511

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

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

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


%d		day of month: 00
%f		fractional seconds: SS.SSS
%H		hour: 00-24
%j		day of year: 001-366
%J		Julian day number (fractional)
%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 (or nearly) strftime()
date(...)		strftime('%Y-%m-%d', ...)
time(...)		strftime('%H:%M:%S', ...)
datetime(...)		strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...)		strftime('%J', ...) -- note-1
unixepoch(...)		strftime('%s', ...) -- note-1

SQLite Requirement Matrix Details lang_datefunc.html

Index Summary Markup Original

SQLite Requirement Matrix Details
lang_datefunc.html