SQLite研究——内置函数

SQLite研究——内置函数

Core Functions

Thecore functions shown below are available by default. Additionalfunctions may be written in C and added to the database engine usingthe sqlite3_create_function() API.

abs(X)Return the absolute value of argument X.coalesce(X,Y,...)Returna copy of the first non-NULL argument. If all arguments are NULL thenNULL 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()Returnthe 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 Xin characters. If SQLite is configured to support UTF-8, then thenumber 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 theuser-function is invoked with three arguments. Otherwise, it is invokedwith two arguments only. The sqlite_create_function() interface can be used to override this function and thereby change the operation of the LIKEoperator. When doing this, it may be important to override both the twoand three argument versions of the like() function. Otherwise,different code may be called to implement the LIKE operator dependingon 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,...)Returnthe argument with the maximum value. Arguments may be strings inaddition to numbers. The maximum value is determined by the usual sortorder. 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,...)Returnthe argument with the minimum value. Arguments may be strings inaddition to numbers. The minimum value is determined by the usual sortorder. 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)Thisroutine returns a string which is the value of its argument suitablefor inclusion into another SQL statement. Strings are surrounded bysingle-quotes with escapes on interior quotes as needed. BLOBs areencoded as hexadecimal literals. The current implementation of VACUUMuses this function. The function is also useful when writing triggersto 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 isomitted 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 Yis negative the the first character of the substring is found bycounting from the right rather than the left. If SQLite is configuredto support UTF-8, then characters indices refer to actual UTF-8characters, 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

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

Inany aggregate function that takes a single argument, that argument canbe preceeded by the keyword DISTINCT. In such cases, duplicate elementsare filtered before being passed into the aggregate function. Forexample, the function "count(distinct X)" will return the number ofdistinct values of column X instead of the total number of non-nullvalues in column X.

avg(X)Return the average value of all non-NULL Xwithin a group. String and BLOB values that do not look like numbersare interpreted as 0. The result of avg() is always a floating pointvalue 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)Returnthe minimum non-NULL value of all values in the group. The usual sortorder is used to determine the minimum. NULL is only returned if allvalues in the group are NULL.sum(X)
total(X)Returnthe numeric sum of all non-NULL values in the group. If there are nonon-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 theSQL standard requires it and most other SQL database engines implementsum() that way so SQLite does it in the same way in order to becompatible. The non-standard total() function is provided as aconvenient way to work around this design problem in the SQL language.

Theresult 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 inputto sum() is neither an integer or a NULL then sum() returns a floatingpoint value which might be an approximation to the true sum.

Sum()will throw an "integer overflow" exception if all inputs are integersor NULL and an integer overflow occurs at any point during thecomputation. 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 November24, 4714 B.C. The julian day number is the preferred internalrepresentation of dates. The strftime() routine returns thedate formatted according to the format string specified as the firstargument. The format string supports most, but not all, of the morecommon substitutions found in the strftime() function from the standardC 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

Informats 5 through 7, the "T" is a literal character separating the dateand the time, as required by the ISO-8601 standard. These formats aresupported in SQLite 3.2.0 and later. Formats 8 through 10 that specifyonly 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 afloating point value.

Modifiers

The time stringcan be followed by zero or more modifiers that alter the date or alterthe 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

Thefirst six modifiers (1 through 6) simply add the specified amount oftime 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 theweekday number is N. Sunday is 0, Monday is 1, and so forth.

The"unixepoch" modifier (12) only works if it immediately follows atimestring in the DDDDDDDDDD format. This modifier causes theDDDDDDDDDD to be interpreted not as a julian day number as it normallywould be, but as the number of seconds since 1970. This modifier allowsunix-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

Thecomputation of local time depends heavily on the whim of localpoliticians and is thus difficult to get correct for all locales. Inthis implementation, the standard C library function localtime() isused to assist in the calculation of local time. Note that localtime()is not threadsafe, so use of the "localtime" modifier is notthreadsafe. Also, the localtime() C function normally only works foryears between 1970 and 2037. For dates outside this range, SQLiteattempts to map the year into an equivalent year within this range, dothe calculation, then map the year back.

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

[Considerinstead, using localtime_r which is reentrant and may be used *without*expensive mutex locking. Although non-standard it's available on mostUnixes --hauk] But it is not available on windows, as far as I amaware. --drh On windows localtime() is thread-safe if the MT C runtimeis used. The MT runtime uses thread-local storage for the staticvariables, the kind functions use.--gr [What about usinglocaltime_r, and on systems where it is unavailable defining it assqliteOsEnterMutext() ; locatime() ; sqliteOsLeaveMutex() so thatnon-windows systems get the maximum advantage, with almost zero codeimpact?] The autoconfigury and patch for localtime_r is here: ¤http://www.sqlite.org/cvstrac/tktview?tn=1906 . 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:
Formy use I added new functions and functionalities to the date functionsthat come with the sqlite 3.3.0 (can be used in older versions as wellwith 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 datebackwards to the beginning of the current minute, hour, week, month,year or day.

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

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

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'

Newfunctions "week_number(date)" returns the week number of the year onthe supplied date parameter, "datetime2seconds(datetime)" return thenumber 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 ¤http://dad-it.com:8080/date.c.diff, I hope it's considered valuable to be merged in the official distribution.