Data types supported by Android SQLite

Sqlite3 supported data types: NULL, INTEGER, REAL, TEXT, BLOB
But in fact, Sqlite3 also accepts the following data types:
smallint a 16-bit integer.
Interger a 32-bit integer.
Decimal (P,S) p exact value and S-Size decimal integer, the exact value p is the total number (digits) size value, s refers to the number of digits after the decimal point. If there is no special designation, the system will be set to p=5; S=0.
Float 32-bit real number.
A double 64-bit real number.
char (n) n-length string, n cannot exceed 254.
The varchar (n) length is not fixed and its maximum length is n, and N cannot exceed 4000.
Graphic (n) is the same as char (n), but its unit is two characters Double-bytes and N cannot exceed 127. This pattern is designed to support two character-length fonts, such as Chinese characters.
Vargraphic (n) variable-length double-character string with a maximum length of n, N cannot exceed 2000
Date contains the year, month, and date.
Time contains hours, minutes, seconds.
Timestamp contains the year, month, day, time, minute, second, 1 per thousand seconds.


SQLite includes the following time/date functions:
DateTime () ......... ........ Date and time of generation
Date () ........ ............ Date generated
Time () ........ ............ Generation time
Strftime () ........ ......... Format the date and time generated by the above three functions

The usage of datetime () is: datetime (date/time, modifier, modifier ...)
The syntax for date () and time () is the same as DateTime ().

In a time/Date function, you can use a string in the following format as a parameter:
Yyyy-mm-dd
YYYY-MM-DD hh:mm
YYYY-MM-DD HH:MM:SS
Yyyy-mm-dd HH:MM:SS. Sss
hh:mm
HH:MM:SS
HH:MM:SS. Sss
Now
Where now is the time to produce the present.

Example (the time to write this note is October 17, 2006 8 o'clock in the evening to 10, test environment: SQLite 2.8.17,WINXP, Beijing Time):


Example 1.select datetime (' Now ');
Result: 2006-10-17 12:55:54

Example 2.select datetime (' 2006-10-17 ');
Result: 2006-10-17 12:00:00

Example 3.select datetime (' 2006-10-17 00:20:00 ', ' +1 hour ', ' -12 minute ');
Result: 2006-10-17 01:08:00

Example 4.select date (' 2006-10-17 ', ' +1 Day ', ' +1 Year ');
Results: 2007-10-18

Example 5.select datetime (' Now ', ' start of Year ');
Result: 2006-01-01 00:00:00

Example 6.select datetime (' Now ', ' start of Month ');
Result: 2006-10-01 00:00:00

Example 7.select datetime (' Now ', ' start of Day ');
Result: 2006-10-17 00:00:00

Example 8.select datetime (' Now ', ' +10 hour ', ' start of day ', ' +10 Hour ');
Result: 2006-10-17 10:00:00

Example 9.select datetime (' Now ', ' localtime ');
Result: 2006-10-17 21:21:47

Example 10.select datetime (' Now ', ' +8 Hour ');
Result: 2006-10-17 21:24:45


The +1 hour and -12 minute in example 3 indicate that a certain amount of time can be increased or decreased at the base time (the first parameter of the DateTime function).

The start of year in Example 5 represents the time of the beginning of the first day.

As can be seen from Example 8, although the 2nd parameter added 10 hours, but was the 3rd parameter "start of day" to zero the time to 00:00:00, followed by the 4th parameter at 00:00:00
On the basis of the time increased by 10 hours into the 10:00:00.

Example 9 converts the Greenwich time zone to the cost of the time zone.

Example 10 converts the Greenwich time zone to the East eight zone.

The strftime () function converts date strings in YYYY-MM-DD HH:MM:SS format to other forms of strings.
The syntax of Strftime () is strftime (format, date/time, modifier, modifier, ...)

It can be formatted with the following symbols for dates and times:
%d month, 01-31
%f decimal form of the second, SS. Sss
%H hours, 00-23
%j figure out the day of the year, 001-366
%m month, 00-12
%M min, 00-59
%s number of seconds from January 1, 1970 to present
%s seconds, 00-59
%w Week, 0-6 (0 is Sunday)
%W calculates the day of the week that falls within that year, 01-53
%Y years, YYYY
Percent hundred percent semicolon

An example of the use of strftime () is as follows:


Example 11.select strftime ('%y.%m.%d%h:%m:%s ', ' Now ', ' localtime ');
Result: 2006.10.17 21:41:09


SQLite function article:
Arithmetic functions
ABS (X) returns the absolute value of the given numeric expression.
Max (x,y[,...]) returns the maximum value of an expression.
Min (x,y[,...]) returns the minimum value of an expression.
Random (*) returns the number.
Round (X[,y]) returns a numeric expression that is rounded to the specified length or precision.
Character processing functions
Length (X) returns the number of characters for the given string expression.
Lower (X) returns a character expression after converting the uppercase character data to lowercase characters.
Upper (X) returns the character expression that converts the lowercase character data to uppercase.
substr (x, y, z) returns part of an expression.
Randstr ()
QUOTE (A)
A like (A, B) determines whether the given string matches the specified pattern.
Glob (A, B)
Conditional Judgment function
COALESCE (x,y[,...])
Ifnull (x, y)
Nullif (x, y)
Aggregate functions
AVG (X) returns the average of the values in the group.
COUNT (X) returns the number of items in the group.
Max (X) returns the maximum value of the value in the group.
MIN (X) returns the minimum value of the value in the group.
SUM (X) returns the sum of all values in an expression.
Other functions
typeof (X) returns the type of data.
Last_insert_rowid () returns the ID of the last inserted data.
Sqlite_version (*) returns the version of SQLite.
Change_count () returns the number of rows affected by the previous statement.
Last_statement_change_count ()

Data types supported by Android SQLite