Small. Fast. Reliable.
Choose any three.
关于 网站地图 文档 下载 许可 新闻 支持

SQLite里的SQL
SQL As Understood By SQLite

[Top]

日期和时间函数
Date And Time Functions

SQLite支持下列5个日期和时间函数：
SQLite supports five date and time functions 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, ...)

这五个日期和时间函数都需要一个时间字符串参数。 时间字符串后面跟着零个或多个修改项。 strftime()函数还需要一个格式化字符串作为第一个参数。
All five date and time functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument.

这些日期和时间函数是IS0-8601日期时间格式的一个子集。 date()函数返回日期格式为：YYYY-MM-DD。 time()函数返回时间格式为：HH:MM:SS。 datetime()函数返回格式为："YYYY-MM-DD HH:MM:SS"。 julianday()函数返回Julian day——从格林威治时间公元前4714年11月24日中午至今的天数(Proleptic Gregorian calendar)。 strftime()函数返回返回一个由第一个参数指定个时间日期格式字符串。格式字符串支持C标准库中strftime() 函数的绝大部分替换符，还额外增加了两个符号：%f和%J。下面是strftime()支持的占位符的完整列表：
The date and time functions use a subset of IS0-8601 date and time formats. 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 Julian day - the number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar). The strftime() routine returns the date formatted according to the format string specified as the first argument. 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. 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
%% %

注意，其他所有的日期时间函数都可以使用strftime()函数来表示：
Notice that all other date and time functions can be expressed in terms of strftime():

函数等价的 strftime()
date(...) strftime('%Y-%m-%d', ...)
time(...) strftime('%H:%M:%S', ...)
datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...) strftime('%J', ...)

提供strftime()以外的函数的唯一原因就是为了使用方便和提高效率。
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. DDDDDDDDDD

在格式5到7中，符号"T"是ISO-8601中要求的用来分割日期和时间的符号。格式8到10中，只指定了时间格式，假定日期为2000-01-01。格式11中，'now'字符串会被替换为从sqlite3_vfs对象的xCurrentTime方法获取的当前日期和时间。多次在同一个sqlite3_step()方法中调用传入'now'参数的时间日期函数总是会返回完全相同的值。通常使用世界标准时间(UTC)。格式12中使用浮点数表示Julian day number
In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by ISO-8601. 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 as obtained from the xCurrentTime method of the sqlite3_vfs object in use. The 'now' argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as a floating point value.

格式2到10中，可以选择跟一个时区标示符："[+-]HH:MM" 或者"Z"。日期时间函数内部使用UTC或者"zulu"时间，因此"Z"后缀是无效的。为了计算zulu时间，任何非零的"HH:MM"后缀都会被从显示的时间日期上删去。例如下面所有的时间字符串都是等价的：
Formats 2 through 10 may be optionally followed by a timezone indicator of the form "[+-]HH:MM" or just "Z". The date and time functions use UTC or "zulu" time internally, and so the "Z" suffix is a no-op. Any non-zero "HH:MM" suffix is subtracted from the indicated date and time in order to compute zulu time. For example, all of the following time strings are equivalent:

2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685

在格式4、5和10中，小数部分的秒值可以在小数点后保留多位。示例中都展示三位是因为只有前三位对结果是有意义的，但是输入字符串可以多于或少于三位，并且日期和时间函数都可以正确的执行。同样的，格式12中展示了10位数字，但是日期时间函数只接受足以表示Julian天数的位数。
SS.SSSIn 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. Similarly, format 12 is shown with 10 significant digits, but the date/time functions will really accept as many or as few digits as are necessary to represent the Julian day number.

修改项
Modifiers

时间字符串可以跟零个或多个修改项用来修改时间或日期。每个修改项都是一个可以应用于其左边的时间值的转换器。修改项按照从左往右的顺序实施，所以顺序是非常重要的。下面是所有可以使用的修改项：
The time string can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. 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

前六个修改项（1到6）只是简单的添加一个指定的时间值到前面的时间串上。注意，在原始日期格式YYYY-MM-DD上使用"±NNN months"就是将±NNN值加到MM月上，然后再标准化输出。因此，例如，在日期2001-03-31上执行修改项'+1 month'会得到结果2001-04-31，但是四月只有30天，所以日期会被标准化为2001-05-01.类似是情况还有当原始日期是闰年的2月29日，当修改项为±N years且N不是4的倍数时。
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. 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. 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. 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.

"start of"修改项（7到9）将当前日期回退到当前月、年、天的开始。
The "start of" modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.

"weekday"修改项将日期向前调整到下一个星期N。星期日是0，星期一是1 等等。
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.

"unixepoch"修改项（11）只能紧跟在DDDDDDDDDD格式的时间串后使用。这个修改项会使DDDDDDDDDD不再被解释为通常的Julian天数，而是Unix 时间——从1970年开始的秒数。如果"unixepoch"修改项没有跟在一个表示从1970年至今的秒数的DDDDDDDDDD格式的时间串后面，或者在"unixepoch"和前面的DDDDDDDDDD之间加入了其他修改项，那么返回结果是未知的。通过使用64位整数来保证时间精度，"unixepoch"修改项可以用于0000-01-01 00:00:00 到 5352-11-01 10:52:47之间的时间（unix时间-62167219200 到 10675199167）。
The "unixepoch" modifier (11) 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 Unix Time - the number of seconds since 1970. If the "unixepoch" modifier does not follow a timestring of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the "unixepoch" modifier from prior DDDDDDDDDD then the behavior is undefined. Due to precision limitations imposed by the implementations use of 64-bit integers, the "unixepoch" modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 10675199167).

"localtime" 修改项（12）假定左边的时间串是在世界标准时间 (UTC)下，然后调整时间串到本地时间。如果"localtime"更在一个非UTC时间后面，那么结果将是未知的。"localtime"相对应的是"utc"。"utc"假定左边的时间串是在本地时区下，然后调整时间串到UTC下。如果左边的字符串不是本地时间，那么"utc"的结果将是未知的。
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. If "localtime" follows a time that is not UTC, then the behavior is undefined. The "utc" 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. If the prior string is not in localtime, then the result of "utc" is undefined.

示例
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');

计算给定的unix时间戳1092941466的日期和时间。
Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');

给定一个unix时间戳1092941466，计算当前时区下的日期和时间。
Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

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

当前unix时间戳。
Compute the current unix timestamp.

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

计算美国宣布独立以来的天数。
Compute the number of days since the signing of the US Declaration of Independence.

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

计算2004年指定的时刻至今的秒数。
Compute the number of seconds since a particular moment in 2004:

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

计算今年十月的第一个周二的日期。
Compute the date of the first Tuesday in October for the current year.

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

计算unix纪元至今的秒数（类似strftime('%s','now')但是要包含小数部分）。
Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):

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

警告和漏洞
Caveats And Bugs

本地时间的计算完全依赖于政治家的幻想，很难在每个时区都获取到正确的时间。在当前实现中使用了C标准库中的localtime_r()函数来帮助计算本地时间。localtime_r()函数通常只能使用在1970年到2037年之间。为了处理这个范围之外的日期，SQLite尝试将范围外的年映射到与之等价的范围内的年上，然后进行计算，最后在映射回原来的年份。
The computation of local time depends heavily on the whim of politicians and is thus difficult to get correct for all locales. In this implementation, the standard C library function localtime_r() is used to assist in the calculation of local time. 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.

这些函数使用使用在日期0000-01-01 00:00:00 到 9999-12-31 23:59:59之间（julidan 天数1721059.5 到 5373484.5）。计算这个日期范围之外的日期的结果是未知的。
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). For dates outside that range, the results of these functions are undefined.

非Windows Vista 平台只能支持一套夏令时（DST）规则。只有Vista可以支持两套。因此在这个平台上遗留的DST计算将会是错误的。例如，在美国，2007年DST规则发生修改。非Windows Vista平台对以前的年份全都适用于新的2007年DST规则。而Vista做的稍微好一点儿当回到1986年当规则发生变化时还能获取正确的结果。
Non-Vista Windows platforms only support one set of DST rules. Vista only supports two. Therefore, on these platforms, historical DST calculations will be incorrect. For example, in the US, in 2007 the DST rules changed. Non-Vista Windows platforms apply the new 2007 DST rules to all previous years as well. Vista does somewhat better getting results correct back to 1986, when the rules were also changed.

所有的内部计算都假定使用阳历（Gregorian calendar）系统。并且假定每天都是精确的86400秒。
All internal computations assume the Gregorian calendar system. It is also assumed that every day is exactly 86400 seconds in duration.