sqlite的使用方法和函数(转)

expression
expr ::=     expr binary-op expr |
expr [NOT] like-op expr [ESCAPE expr] |
unary-op expr |
( expr ) |
column-name |
table-name . column-name |
database-name . table-name . column-name |
literal-value |
parameter |
function-name ( expr-list | * ) |
expr ISNULL |
expr NOTNULL |
expr [NOT] BETWEEN expr AND expr |
expr [NOT] IN ( value-list ) |
expr [NOT] IN ( select-statement ) |
expr [NOT] IN [database-name .] table-name |
[EXISTS] ( select-statement ) |
CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END |
CAST ( expr AS type ) |
expr COLLATE collation-name
like-op ::=     LIKE | GLOB | REGEXP | MATCH

这部分有别于其它部分。本文档的其它大多数部分都在讲特定的 SQL 命令。本部分所讲的不是一个单一的命令，而是“表达式”，它经常用作其它命令的一部分来使用。

SQLite 有如下二元运算符，根据其优先级从高到低有：

    ||
    *    /    %
    +    -
    <<   >>   &    |
    <    <=   >    >=
    =    ==   !=   <>   IN
    AND  
    OR

以下是支持的一元运算符：

    -    +    !    ~    NOT

COLLATE 运算符可以被看作是一个后置的一元运算符它具有最高的优先级。通常，与其前面的一元、及二元运算符相比，它与参数结合更为紧密。

一元 [Operator +] 什么也不做。它可以被应用于字符串，数字，或BLOB，并永远返回跟它作用的数值相同的结果。

注意，两种变体的等号运算符是不一样的。等可以是 = 或 ==。不等操作符有 != 或 <>。 || 运算符为“连接符”，它将两个字符串连接到一起。 % 输出其左边的数除以右面数后的余数。

除 || 之外，任何二元操作符的结果都是一个数值型的值。 || 返回两个操作数连接后的大字符串。

字 面值是一个整数或浮点数的值。也支持科学记数法。小数点永远使用“.” 字符来表示，即使本地设置指定用“,” 来表示也不例外。在这种规则下，使用“,” 作小数点会引起语义上的二义性。字符串值应该用单引号（'） 引起来。像在 Pascl 语言中那样，字符串中的单引号应该使用两个单引号表示。由于像 C 语言那样使用反斜线进行转义的方式不是 SQL 语言的标准，所以不支持那种类型的转义。 BLOB字面值是以一个“x” 或“X” 打头的包含十六进制数据的字符串值。例如：

    X'53514C697465'

字面值也可以是 "NULL" 记号。

参数用于指定一个字面值在表达式中的存放位置。它将在运行时被使用 sqlite3_bind API 替换。参数有如下几种格式：

    ?NNN        问号后跟一个数字 NNN 用于存放第 NNN 个参数。 NNN 必须在 1 到 999 之间。
    ?        只有一个问号，它将用于存放紧跟其后的一个未用到的参数。
    :AAAA        冒号后面跟一个标志符，用于存放名字为 AAAA 的变量。命名的变量也会被编号，编号将使用第一个未被使用的编号。为避免混淆，最好不要混用名字和编号作为参数。
    @AAAA        一个 @ 符号等价于一个冒号。
    $AAAA        美元符号后跟一个标志符也会存放名字为 AAAA 的参数。在这种情况下，参数名字中可以出现很多“::”，并且，在“(...)”后缀中可以包含任何文本。该语法是由 TCL 语言中相同的法而来的。

未使用 sqlite3_bind 来赋值的参数则认为是 NULL。

LIKE 操作符会作一个模式匹配比较。它右边是一个匹配模式，左边包含被匹配的字符串。 在匹配模式中，百分号 % 会匹配字符串中任意 0 个或多个字符。一个下划线 _ 符号仅匹配一个任意的字符。除此之外，其它的任何字符均只匹配它们自己（在不区分大小写的环境下可能会匹配与之对应的大、小写字母）。（一个 BUG：SQLite中不区分大小写仅对 7 比特的拉丁字符有效，对于 8 位的 iso8859 字符集UTF8字符则是区分大小写的）。例如：表达式 'a' LIKE 'A' 是 TRUE 但 'æ' LIKE 'Æ' 则是 FALSE)。

若 有可选的 ESCAPE 子句，那么 ESCAPE 关键字后的表达式必须是一个单个的字符（叫做转义字符）。该字符可以用于 LIKE 模式字符串中来体现百分号或下划线。转义字符后面的百分号或下划线均分别保持它们原来的意思。中缀的 LIKE 操作符是使用 like(X,Y) 函数实现的。
LIKE 操作符不区分大小写，它将匹配一边是小写而另一边是大写的字符串。（一个BUG：SQLite中不区分大小写仅对 7 比特的拉丁字符有效，对于 8 位的 iso8859 字符集UTF8字符则是区分大小写的）。例如：表达式 'a' LIKE 'A' 是 TRUE 但 'æ' LIKE 'Æ' 则是 FALSE)。

中缀操作符 LIKE 是通过调用用户函数 like(X,Y) 实现的。但若后面还有 ESCAPE 子句，它会在以上函数中增加第三个参数。如果该函数被其它的 like() SQL 函数重载，则需要注意。

GLOB 操作符与 LIKE 类似，但它使用 Unix 通配符的的文件匹配语法。并且，与 LIKE 不同， GLOB 是大小写敏感的。GLOB 和 LIKE 都可以用 NOT 关键字对匹配结果取反。 中缀的 GLOB 操作符是通过调用用户函数 glob(X,Y) 实现的，可以通过该函数对其进行重载。

REGEXP 操作符是使用 regexp() 用户函数的一个特殊语法。默认情况下， regexp() 用户函数没有定义，所以使用 REGEXP 将会出错。如果在运行时增加一个名为 regexp 的用户定义函数，则使用该操作符时将使用此函数来实现 REGEXP 功能。

MATCH 操作符是使用 match() 用户函数的一个特殊语法。默认的 match() 函数实现只会引发一个异常，从而没多大用处。但可以通过扩展该函数来实现更有用的逻辑。

列 名colum name 可以是任何在 CREATE TABLE 语句中指定的列名，也可以是如下一个特殊标志符： "ROWID"、"OID" 或 "_ROWID_"。这些特殊标志符均描述与每个表每一行所关连的唯一的整数键。特殊标志符只会在 CREATE TABLE 语句没有指定相同的列名时才会真正指代每一行。行键值就像只读的列。一个行键值可以用在任何一个正常列所能使用的地方。只是，不能使用 UPDATE 或 INSERT 来改变行的键值。 “SELECT * ...”也不会返回行键值。

SELECT 语句可以出现在 IN 操作符的后面，或作为一个单独的数量值出现，或者跟在 EXISTS 操作符后面。当作为一个单独数量值或在 IN 操作符中时，SELECT 必须只能返回单列。复合 SELECT 查询 （使用 UNION 或 EXCEPT 关键字的查询）也是允许的。当使用 EXISTS 关键字时，SELECT 结果中的列被忽略。如果 SELECT 返回了一行或多行，则表达式就为 TRUE，否同就为 FALSE。如果内层 SELECT 表达式中的项目与外层的值无关，则内层 SELECT 会最先求值，并在以后需要时重用该值。如果内层 SELECT 中含有外层查询相关的变量，那么内层 SELECT 会在每次需要的时候被重新求值。

当一个 SELECT 作为 IN 操作符的右操作数时，如果 IN 左边的操作数存在于右边 SELECT 的查询结果中， IN 就返回 TRUE。 IN 操作符也可以搭配 NOT 来对表达式结果取反。

当一个 SELECT 出现在除 IN 之外的其它表达式中时， SELECT 结果的第一行将作为一个单一的值用于该表达式中。如果 该SELECT 返回多行，那么其它的行将会被忽略。若 SELECT 返回 0 行，则其结果将是 NULL。

一个 CAST 表达式会将 的数据类型转换为指定的类型 。其中 可以是任何在 CREATE TABLE 语句中有效的非空的类型名。

系统支持简单和***函数。一个简单函数可以用于任何表达式。简单函数会在其输入的基础上立即返回结果。***函数只能用于 SELECT 语句中。 ***函数会在返回的结果集上进行跨行计算，并返回相应的结果。
核心函数 Core Functions

下列是默认可以的核心函数。额外的函数可以使用 C 语言编写并使用 sqlite3_create_function() API 加入数据库引擎中。
abs(X)     返回参数 X的绝对值。
coalesce(X,Y,...)     返回参数中第一个非 NULL 的值。若所有参数均为 NULL，则返回 NULL。该函数至少需要两个参数。
glob(X,Y)     该函数用户实现 SQLite 的 "X GLOB Y" 语法。可以使用 sqlite3_create_function() 接口来重载该函数，从而改变 GLOB 操作符的行为方式。
ifnull(X,Y)     返回第一个非 NULL 的参数值，如果所有参数都是 NULL， 则返回 NULL。与上面的 coalesce() 一样。
hex(X)     参数以 BLOB 对待。结果是 BLOB 内容的 16 进制表示。
last_insert_rowid()     返回当前数据库连接中最后插入的一行的 ROWID。该值与使用 sqlite_last_insert_rowid() API 函数返回的值相同。
length(X)     返回 X 字符串的长度。如果 SQLite 配置为支持 UTF-8，则返回的是 UTF-8 字符的长度，而不是字节数。
like(X,Y)
like(X,Y,Z)     该函数用于实现 SQL 的 "X LIKE Y [ESCAPE Z]" 语法。如果有可选的 ESCAPE 语句，将使用具有三个参数的函数，否则，将使用只有两个参数的数据。可以使用 sqlite_create_function() 接口来重载该函数，以改变 LIKE 操作符的行为。如果那样做，一定要注意同时重载两个（两个参数的和三个参数的）版本的函数。否则，依据是否使用了 ESCAPE 子句，可以会调用不同的代码。
load_extension(X)
load_extension(X,Y)     装入 SQLite 共享库之外文件名为 X 而入口点为 Y 的扩展库。结果将是 NULL。 如果省略了 Y，那么，将使用默认的入口点 sqlite3_extension_init。该函数在装入或初始化失败时会引发一个异常。

如果扩展试图修改或删除一个 SQL 函数或对照序列，则该函数会失败。可以使用扩展增加新函数或对照序列，但不能修改或删除已存在的。这是因为那些函数和/或对照序列可能正在被其它的SQL 语句使用。要想装入可以修改或删除函数或对照序列的扩展，使用 sqlite3_load_extension() C 语言API。
lower(X)     返回将字符串 X 转换为小写后的字符串。该函数使用 C 语言库函数 tolower() 进行转换，所以，可能不能正确转换 UTF-8 字符。
ltrim(X)
ltrim(X,Y)     返回一个字符串，它是从字符串 X 的左边删除了任何存在于字符串 Y 中的字符后剩余的字符串。如果省略参数 Y ，则会删除左边的空格。
max(X,Y,...)     返回参数的最大值。除数字外，参数可能是字符串。它使用通常的排序顺序来决定最大值。注意若 max() 有两个或多个参数，则它是一个简单函数。但如果只提供一个参数，它将变成一个***函数。
min(X,Y,...)     返回参数的最小值。除数字外，参数可能是字符串。它使用通常的排序顺序来决定最小值。注意若 min() 有两个或多个参数，则它是一个简单函数。但如果只提供一个参数，它将变成一个***函数。
nullif(X,Y)     如果参数不同，则返回第一个参数，否则返回NULL。
quote(X)     该函数返回一个对其参数值进行处理后适合包含在其它 SQL 语句中的字符串。单引号包括的字符串将会按需要转换成内部引用的格式。 BLOB 将会编码为十六进制字面值。当前实现的 VACUUM 使用该函数。在写触发器来实现 “撤消/重做” 功能时，该函数也是很有用的。
random(*)     返回一个伪随机数。结果在 -9223372036854775808 与 +9223372036854775807之间。
replace(X,Y,Z)     返回一个将 X 字符串中每一个出现 Y 的位置替换为 Z 后的字符串。它使用二进制对照序列进行比较。
randomblob(N)     返回一个 N 字节长的包含伪随机字节的 BLOG。 N 应该是正整数。
round(X)
round(X,Y)     对 X 圆整成小数点后 Y 位。或省略 Y ，则默认Y 为 0。
rtrim(X)
rtrim(X,Y)     返回从 X 的右边去除所有出现在 Y 中字符以后的字符串。如果省略 Y，则去除空格。
soundex(X)     计算字符串 X的读音编码。如果参数为 NULL，则返回 "?000"。默认情况下 SQLite 忽略该函数。仅当在编译时指定 -DSQLITE_SOUNDEX=1 时才有效。
sqlite_version(*)     返回当前运行的 SQLite 库的版本号。如："2.8.0"
substr(X,Y,Z)
substr(X,Y)     返回字符串 X 从第 Y 个字符开始，长度为 Z 的字符串。如果省略 Z， 将返回直到字符串结尾的字符串。 X 的第一个字符从 1 开始。如果 Y 是负数，则从右边开始数。如果 X 是 UTF-8 字符串，那么，下标值将指实际的 UTF-8 characters 字符，而不是字节。如果 X 是一个 BLOB，那么下标就是指字节。
trim(X)
trim(X,Y)     返回从字符串 X 的两头去掉所有存在于 Y 中字符后的字符串。如果省略 Y，则去空格。
typeof(X)     返回表达式 X 的类型。只可能是 "null", "integer", "real", "text", 以及 "blob"。 SQLite 的类型处理在 Datatypes in SQLite Version 3 中有说明。
upper(X)     返回字符串 X 被转换为大写后的字符串。它使用 C 库函数 toupper() 实现，对于 UTF-8，某些字符串可能不能正确转换。
zeroblob(N)     返回一个 N 字节长、全部由 0x00 组成的 BLOB。 SQLite 或以很有效的组织这些 zeroblob。它可以被用于为以后的使用预留空间。以后可以使用 incremental BLOB I/O 来写入 BLOB 数据。
日期和时间函数

日期和时间函数在 SQLite Wiki上。
***函数

有下列默认可和的***函数：可以使用 sqlite3_create_function() API 来增加其它的***函数。

任何***函数都只有一个参数。其参数前可以有一个 DISTINCT。如果有，重复的元素将会在传递给***函数前过滤掉。如，函数 "count(distinct X)" 将只返回在 X 列上不重复的行的总数，而不是在该列上所有非空的行的总数
avg(X)     返回 X 列的一组中所有非空值的平均值。字符串或 BLOB 等非数字值将被认为是 0。即使所有输入都是整数， avg() 的结果也永远是浮点数。
count(X)
count(*)     第一种形式返回在 X列上的一组中非空的行的总数。第二种（无任何参数）返回一组中所有的行数。
group_concat(X)
group_concat(X,Y)     结果是一个所有非空的 X 连接起来的一个字符串。如果有参数 Y，则它会做为连接 X 时的分隔符。若省略Y ，则默认是逗号（,）。
max(X)     返回一组中的最大值。使用通常的排序顺序来确定最大值。
min(X)     返回一组中除非空值以外的最小值。使用通常的排序顺序来确实最小值。只有当整个组中所有值均为 NULL 时才会返回 NULL。
sum(X)
total(X)     返回一组中所有非空值的数值总和。如果在输入中没有非空值，则 sum() 会返回 NULL 而 total() 会返回 0.0。不仅能在 sum() 没有对任何行求和时， NULL能给出有帮助的结果，而且 SQL 标准也需要它。而且其它的 SQL 数据库引擎也是这么实现的。 SQLite 这么做也是为了保持兼容性。我们也提供了一个非标准的 total() 函数，以提供一个方便的途径来绕过 SQL 语言的这一设计问题。

total() 的结果永远是浮点数。 如果所有非空的输入都是整数， sum() 的结果将是整数值。 若任何输入给 sum() 的值是除整数及 NULL 以外的值， sum() 都将返回浮点数。这可能是最接近标准 sum() 的实现方式吧。

如果输入全部是整数或 NULL，在结果溢出时 sum() 将会产生一个 "integer overflow" 异常。 而 total() 永远不会。

限制笔数
select * from film limit 10;
sqlite3 film.db "select * from film;"

输出 HTML 表格：

sqlite3 -html film.db "select * from film;"

将数据库「倒出来」：

sqlite3 film.db ".dump" > output.sql

利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的SQL数据库备份了）：

sqlite3 film.db < output.sql

在大量插入资料时，你可能会需要先打这个指令：

begin;

插入完资料后要记得打这个指令，资料才会写进数据库中：

commit;


SQLite内建函数表
算术函数
abs(X)    返回给定数字表达式的绝对值。
max(X,Y[,...])    返回表达式的最大值。
min(X,Y[,...])    返回表达式的最小值。
random(*)    返回随机数。
round(X[,Y])    返回数字表达式并四舍五入为指定的长度或精度。
字符处理函数
length(X)    返回给定字符串表达式的字符个数。
lower(X)    将大写字符数据转换为小写字符数据后返回字符表达式。
upper(X)    返回将小写字符数据转换为大写的字符表达式。
substr(X,Y,Z)    返回表达式的一部分。
randstr()  
quote(A)  
like(A,B)    确定给定的字符串是否与指定的模式匹配。
glob(A,B)  
条件判断函数
coalesce(X,Y[,...])  
ifnull(X,Y)  
nullif(X,Y)  
集合函数
avg(X)    返回组中值的平均值。
count(X)    返回组中项目的数量。
max(X)    返回组中值的最大值。
min(X)    返回组中值的最小值。
sum(X)    返回表达式中所有值的和。
其他函数
typeof(X)    返回数据的类型。
last_insert_rowid()    返回最后插入的数据的ID。
sqlite_version(*)    返回SQLite的版本。
change_count()    返回受上一语句影响的行数。
last_statement_change_count()


The core functions shown below are available by default. Additional functions may be written in C and added to the database engine using the sqlite3_create_function() API.
abs(X)     Return the absolute value of argument X.
coalesce(X,Y,...)     Return a copy of the first non-NULL argument. If all arguments are NULL then NULL is returned. There must be at least 2 arguments.
glob(X,Y)     This function is used to implement the "X GLOB Y" syntax of SQLite. The sqlite3_create_function() interface can be used to override this function and thereby change the operation of the GLOB operator.
ifnull(X,Y)     Return a copy of the first non-NULL argument. If both arguments are NULL then NULL is returned. This behaves the same as coalesce() above.
last_insert_rowid()     Return the ROWID of the last row insert from this connection to the database. This is the same value that would be returned from the sqlite_last_insert_rowid() API function.
length(X)     Return the string length of X in characters. If SQLite is configured to support UTF-8, then the number of UTF-8 characters is returned, not the number of bytes.
like(X,Y [,Z])     This function is used to implement the "X LIKE Y [ESCAPE Z]" syntax of SQL. If the optional ESCAPE clause is present, then the user-function is invoked with three arguments. Otherwise, it is invoked with two arguments on

ly. The sqlite_create_function() interface can be used to override this function and thereby change the operation of the LIKE operator. When doing this, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.
load_extension(X)
load_extension(X,Y)     Load SQLite extensions out of the shared library file named X using the entry point Y. The result is a NULL. If Y is omitted then the default entry point of sqlite3_extension_init is used. This function raises an exception if the extension fails to load or initialize correctly.
lower(X)     Return a copy of string X will all characters converted to lower case. The C library tolower() routine is used for the conversion, which means that this function might not work correctly on UTF-8 characters.
max(X,Y,...)     Return the argument with the maximum value. Arguments may be strings in addition to numbers. The maximum value is determined by the usual sort order. Note that max() is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.
min(X,Y,...)     Return the argument with the minimum value. Arguments may be strings in addition to numbers. The minimum value is determined by the usual sort order. Note that min() is a simple function when it has 2 or more arguments but converts to an aggregate function if given only a single argument.
nullif(X,Y)     Return the first argument if the arguments are different, otherwise return NULL.
quote(X)     This routine returns a string which is the value of its argument suitable for inclusion into another SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals. The current implementation of VACUUM uses this function. The function is also useful when writing triggers to implement undo/redo functionality.
random(*)     Return a pseudo-random integer between -9223372036854775808 and +9223372036854775807.
round(X)
round(X,Y)     Round off the number X to Y digits to the right of the decimal point. If the Y argument is omitted, 0 is assumed.
soundex(X)     Compute the soundex encoding of the string X. The string "?000" is returned if the argument is NULL. This function is omitted from SQLite by default. It is only available the -DSQLITE_SOUNDEX=1 compiler option is used when SQLite is built.
sqlite_version(*)     Return the version string for the SQLite library that is running. Example: "2.8.0"
substr(X,Y,Z)     Return a substring of input string X that begins with the Y-th character and which is Z characters long. The left-most character of X is number 1. If Y is negative the the first character of the substring is found by counting from the right rather than the left. If SQLite is configured to support UTF-8, then characters indices refer to actual UTF-8 characters, not bytes.
typeof(X)     Return the type of the expression X. The only return values are "null", "integer", "real", "text", and "blob". SQLite's type handling is explained in Datatypes in SQLite Version 3.
upper(X)     Return a copy of input string X converted to all upper-case letters. The implementation of this function uses the C library routine toupper() which means it may not work correctly on UTF-8 strings.

Aggregate Functions

The aggregate functions shown below are available by default. Additional aggregate functions written in C may be added using the sqlite3_create_function() API.

In any aggregate function that takes a single argument, that argument can be preceeded by the keyword DISTINCT. In such cases, duplicate elements are filtered before being passed into the aggregate function. For example, the function "count(distinct X)" will return the number of distinct values of column X instead of the total number of non-null values in column X.
avg(X)     Return the average value of all non-NULL X within a group. String and BLOB values that do not look like numbers are interpreted as 0. The result of avg() is always a floating point value even if all inputs are integers.
count(X)
count(*)     The first form return a count of the number of times that X is not NULL in a group. The second form (with no argument) returns the total number of rows in the group.
max(X)     Return the maximum value of all values in the group. The usual sort order is used to determine the maximum.
min(X)     Return the minimum non-NULL value of all values in the group. The usual sort order is used to determine the minimum. NULL is only returned if all values in the group are NULL.
sum(X)
total(X)     Return the numeric sum of all non-NULL values in the group. If there are no non-NULL input rows then sum() returns NULL but total() returns 0.0. NULL is not normally a helpful result for the sum of no rows but the SQL standard requires it and most other SQL database engines implement sum() that way so SQLite does it in the same way in order to be compatible. The non-standard total() function is provided as a convenient way to work around this design problem in the SQL language.

The result of total() is always a floating point value. The result of sum() is an integer value if all non-NULL inputs are integers. If any input to sum() is neither an integer or a NULL then sum() returns a floating point value which might be an approximation to the true sum.

Sum() will throw an "integer overflow" exception if all inputs are integers or NULL and an integer overflow occurs at any point during the computation. Total() never throws an exception.

Date and Time Functions Overview

Five date and time functions are available, as follows:

   1. date( timestring, modifier, modifier, ...)
   2. time( timestring, modifier, modifier, ...)
   3. datetime( timestring, modifier, modifier, ...)
   4. julianday( timestring, modifier, modifier, ...)
   5. strftime( format, timestring, modifier, modifier, ...)

All five functions take a time string as an argument. This time string may be followed by zero or more modifiers. The strftime() function also takes a format string as its first argument.

The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD HH:MM:SS". The julianday() function returns the number of days since noon in Greenwich on November 24, 4714 B.C. The julian day number is the preferred internal representation of dates. The strftime() routine returns the date formatted according to the format string specified as the first argument. The format string supports most, but not all, of the more common substitutions found in the strftime() function from the standard C library:

%d day of month
   %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 sunday==0
   %W week of year 00-53
   %Y year 0000-9999
   %% %

The %f and %J conversions are new. Notice that all of the other four functions could be expressed in terms of strftime().

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

The only reasons for providing functions other than strftime() is for convenience and for efficiency.

Time Strings

A time string can be in any of the following formats:

   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. DDDD.DDDD

In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by the ISO-8601 standard. These formats are supported in SQLite 3.2.0 and later. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time. Universal Coordinated Time (UTC) is used. Format 12 is the julian day number expressed as a floating point value.

Modifiers

The time string can be followed by zero or more modifiers that alter the date or alter the interpretation of the date. The available modifiers are as follows.

   1. NNN days
   2. NNN hours
   3. NNN minutes
   4. NNN.NNNN seconds
   5. NNN months (see #551 and [1163])
   6. NNN years (see #551 and [1163])
   7. start of month
   8. start of year
   9. start of week (withdrawn -- will not be implemented)
10. start of day
11. weekday N (see #551 and [1163])
12. unixepoch
13. localtime
14. utc

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

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

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

The "unixepoch" modifier (12) only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a julian day number as it normally would be, but as the number of seconds since 1970. This modifier allows unix-based times to be converted to julian day numbers easily.

The "localtime" modifier (13) adjusts the previous time string so that it displays the correct local time. "utc" undoes this.

Examples

Compute the current date.

SELECT date('now');

Compute the last day of the current month.

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

Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');

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

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

Compute the current unix timestamp.

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

Compute the number of days since the battle of Hastings.

SELECT julianday('now') - julianday('1066-10-14','gregorian');

Compute the number of seconds between two dates:

SELECT julianday('now')*86400 - julianday('2004-01-01 02:34:56')*86400;

Compute the date of the first Tuesday in October (January + 9) for the current year.

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

Caveats And Bugs

The computation of local time depends heavily on the whim of local politicians and is thus difficult to get correct for all locales. In this implementation, the standard C library function localtime() is used to assist in the calculation of local time. Note that localtime() is not threadsafe, so use of the "localtime" modifier is not threadsafe. Also, the localtime() 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.

Please surround uses of localtime() with sqliteOsEnterMutex() and sqliteOsLeaveMutex() so threads using SQLite are protected, at least! -- e It is so. --drh

[Consider instead, using localtime_r which is reentrant and may be used *without* expensive mutex locking. Although non-standard it's available on most Unixes --hauk] But it is not available on windows, as far as I am aware. --drh On windows localtime() is thread-safe if the MT C runtime is used. The MT runtime uses thread-local storage for the static variables, the kind functions use.--gr [What about using localtime_r, and on systems where it is unavailable defining it as sqliteOsEnterMutext() ; locatime() ; sqliteOsLeaveMutex() so that non-windows systems get the maximum advantage, with almost zero code impact?] The autoconfigury and patch for localtime_r is here: ¤ . I'm curious why this obvious fix is not applied. gmtime() also suffers from this same threadsafety problem.

Date computations do not give correct results for dates before Julian day number 0 (-4713-11-24 12:00:00).

All internal computations assume the Gregorian calendar system.
An anonymous user adds:
For my use I added new functions and functionalities to the date functions that come with the sqlite 3.3.0 (can be used in older versions as well with small effort).

In main lines they are as follows:

   1. NNN days
   2. NNN hours
   3. NNN minutes
   4. NNN.NNNN seconds
   5. NNN months (see #551 and [1163])
   6. NNN years (see #551 and [1163])
   7. start of month
   8. start of year
   9. start of week (!!! implemented)
10. start of day
11. weekday N (see #551 and [1163])
12. unixepoch
13. localtime
14. utc
15. julian (not implemented as of 2004-01-05)
16. gregorian (not implemented as of 2004-01-05)
17. start of minute
18. start of hour
19. end of minute
20. end of hour
21. end of day
22. end of week
23. end of month
24. end of year
25. group seconds by
26. group minutes by
27. group hours by
28. group days by
29. group weeks by
30. group months by
31. group years by

The "start of" modifiers (7 through 10 and 17 through 18) shift the date backwards to the beginning of the current minute, hour, week, month, year or day.

The "end of" modifiers (19 through 24) shift the date forwards to the end of the current minute, hour, week, month, year or day.

The "group * by" modifiers (25 through 31) round the date to the closest backward multiple supplied, with some limitations, to the current seconds (1 through 30), minutes (1 through 30), hours (1 through 12), days (1 through 15), weeks (1 through 26), months (1 through 6), years (1 through 100), these limitations are due to dont complicate the calculations when a multiple can span beyound the unit modified.

Ex:

SELECT datetime('2006-02-04 20:09:23','group hours by 3'); => '2006-02-04 18:00:00'

SELECT datetime('2006-02-05 20:09:23','group days by 3'); => '2006-02-04 00:00:00'

New functions "week_number(date)" returns the week number of the year on the supplied date parameter, "datetime2seconds(datetime)" return the number of seconds from the supplied datetime parameter.

The diff file ready to be applied to the file "date.c" in the src directory of sqlite 3.3.0 is at ¤ I hope it's considered valuable to be merged in the official distribution.