sqlite研究——内置函数
Core Functions
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 only. 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,...) | 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,...) | 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,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:
- date( timestring,modifier,...)
- time( timestring,...)
- datetime( timestring,...)
- julianday( timestring,...)
- strftime( format,timestring,...)
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:
- 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
- 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.
- NNN days
- NNN hours
- NNN minutes
- NNN.NNNN seconds
- NNN months (see #551 and [1163] )
- NNN years (see #551 and [1163] )
- start of month
- start of year
- start of week (withdrawn -- will not be implemented)
- start of day
- weekday N (see #551 and [1163] )
- unixepoch
- localtime
- 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: ¤ 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:
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:
- NNN days
- NNN hours
- NNN minutes
- NNN.NNNN seconds
- NNN months (see #551 and [1163] )
- NNN years (see #551 and [1163] )
- start of month
- start of year
- start of week (!!! implemented)
- start of day
- weekday N (see #551 and [1163] )
- unixepoch
- localtime
- utc
- julian (not implemented as of 2004-01-05)
- gregorian (not implemented as of 2004-01-05)
- start of minute
- start of hour
- end of minute
- end of hour
- end of day
- end of week
- end of month
- end of year
- group seconds by
- group minutes by
- group hours by
- group days by
- group weeks by
- group months by
- 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,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 ¤ http://dad-it.com:8080/date.c.diff,I hope it's considered valuable to be merged in the official distribution.
