Android Sqlite3 time Comparison syntax introduction

Data types supported by Sqlite3:

NULL, INTEGER, REAL, TEXT, BLOB
However, Sqlite3 also supports the following data types
smallint16-bit integer
Integer 32-bit integer
Decimal (P,s)P is the exact value, S is the number of decimal digits
Float 32-bit real number
Double64-bit real number
CHAR (n) N-length string, cannot exceed 254
VARCHAR (n)Length not fixed maximum string length of n,n not exceeding 4000
Graphic (n)Same as char (n), but the unit is two characters double-bytes,n not more than 127 (Chinese text)
Vargraphic (N)Variable length and maximum length of n

DateContains the year, month, date
Time Contains hours, minutes, seconds
TimestampContains year, month, day, time, minute, second, 1 per thousand seconds

functions supported by Sqlite3

"1" Date function

datetime ()   : Date and time of Generation
Date (): Generation date
Time (): Generation Times
Strftime (): Formats the date and time generated by the above 3 functions

Usages:
1, select Date (' 2011-9-9 ', ' +1 Day ', ' +1 Year '), and the result is 2010-09-10
2, select DateTime (' Now '); current date and time
3, select datetime (' Now ', ' start of Month '); the first day of the month can also be set on the first day of the year
4, select datetime (' Now ', ' +1 hour ', ' -12 minute ‘); Current time plus 48 minutes

strftime () function to convert a date string in YYYY-MM-DD HH:MM:SS format to another form of string

%d: Days, 01-31
%f: Seconds in decimal form, SS. SSS
% H: Hours
%j   : Day of the year, 001-366
%m: month, 00-12
% m: minutes, 00-59
%s: Seconds from 1970 to present
% s: seconds , 00-59
%w: Week, 0-6,0 is Sunday
% w: One day is the first week of the year, 01-53
% Y: year, YYYY
% percent Sign

Application example:
SELECT strftime ('%y.%m.%d %h:%m:%s ', ' Now ', ' localtime ');

second," Arithmetic function "

Abs (X): return absolute value
Max (x,y[,...]) : Returns the maximum value of
min (x,y,[,...]) : Returns the minimum value
Random (*): Returns a random number
round (x[,y]): Rounding

III" string handler

Length (x): Returns the number of string characters
lower (x): uppercase to lowercase
Upper (x): lowercase to uppercase
substr (x, Y, z): Intercept substring
like (A, B): Determines whether the given string matches the specified pattern

Iv." conditional judgment function, aggregate function, other function "

typeof (X): Type of Return Data
Last_insert_rowid () : Returns the ID of the last inserted data

*************************************************************************************************************** *****

Sqlite3 provides a C function interface to manipulate the Sqlite3 database, which has a key data structure Sqlite3 * type

1. Open the Database
int sqlite3_open (filename, sqlite3 * *);-If the file name does not exist, it will be created automatically
return SQLITE_OK indicates that the operation is normal, the definitions of these macros are defined in the Sqlite3.h file, and the source code will understand more

2. Close the database
int Sqlite3_close (sqlite3 *);

3. SQL statement operation
int sqlite3_exec (sqlite3 *,const char *sql, sqlite3_callback,void *,char **errmsg);
This is the function that executes an SQL statement
pointer to the parameter 1:open function
parameter 2: An SQL statement ending with '/ '
The parameter 3:sqlite3_callback is a callback, and when this statement executes, Sqlite3 will invoke the function you provided, and the callback function will consult the data
parameter 4:void * is the pointer you provide, you can pass any pointer here, this parameter will eventually be passed to the callback function, if you do not need
passed to the callback function, you can set to null
parameter 5: Error message, when execution fails, you can consult this pointer, you can use printf ("%s\n", errmsg) to get a string of information,
This information indicates where the error occurred

Typically, sqlite3_callback and void * are set to NULL, which means that callbacks are not required, such as INSERT, delete operations, and there is no need to use callbacks, and callbacks are used when select is used. Because sqlite3 the data, we have to use a callback to explain what data was detected.

Android Sqlite3 time Comparison syntax introduction