Android Sqlite's summary of Date operations

Android Sqlite's summary of Date operations
First, the time processing of the sqlite database is different from that of SQL Server and that of oracle. The following is a summary based on your own instances.A Log data table is created:


LogID SourceID OperatorID LogType LogLevel LogTime           LogContent
1 aaa. aspx 0 2 1 2011-08-18 16:44:32. 000 aaaa
2 bbb. aspx 1 2 2 2011-08-18 16:38:32. 000 bbbb
3 ccc. aspx 2 3 3 2011-09-02              Cccc
4 ddd. aspx 3 1 4 2011-08-15              Dddd
5 eee. aspx 4 1 3 2011-08-18              Eee

The common SQL Server query statement is as follows: select * from Log where LogTime = '2017-09-02 'can query the data of "'2017-09-02.

In the sqlite database, write the following statement, and no data can be queried. It means that SQL Server and sqlite have different processing time. So how can I write an SQL statement with time as the query condition to query the desired results in the sqlite database? See ::::

Select * from Log where datetime (LogTime) = datetime ('2017-08-18 16:38:32. 000 ')

Sqlite data query statement, must convert the time field and the input time parameter. That is, add datetime () conversion.

After executing the preceding statement, you can find the result. If the SQL Server statement is followed, no query results will be obtained.

In addition, some wrong query statements are as follows:

1: select * from Log where datetime (LogTime) = datetime ('1970-08-18 '). This statement can only query one piece of data, that is, the one corresponding to LogID = 5, instead of obtaining data with LogID = 1 and LogID = 2. It can be seen that sqlite is very strict with time. High accuracy.

2: select * from Log where datetime (LogTime) = datetime ('2017-8-18 '). This statement does not return any results, because the time required for sqlite is yyyy-MM-dd Or yyyy-MM-dd hh: mm: ss. If the number is less than 10 in the current month, it must be written in the form of 0x (for example, 05). Therefore, you must handle the warehouse receiving time during development.

3: select * from Log where datetime (LogTime) = datetime ('1970-08-9 '). Similarly, no results can be found in this Statement. Change '9' to '09, the query result is displayed.


SQLite Date and Time Functions
SQLite does not have the datatime field type, but time can be stored in string type fields,
And provides some useful date and time operation functions.

Strftime (date and time format, date and time string, modifier, modifier ,......)
Strftime (date and time string) is equivalent to the following in AAuto:
Time (date and time string, date and time format). The format syntax used by sqlite and AAuto is the same.
Reference: http://www.aau.cn/doc/reference/libraries/kernel/time/time.html

The strftime () function returns a formatted date and time,
It can format the date and time with the following symbol:

% DDay January
% FSeconds in decimal form, SS. SSSS
% HHour 00-24
% JDay of the year 01-366
% JJulian Day Numbers
% MMonth 01-12
% MMinute 00-59
% SNumber of seconds from January 1
% SSeconds 00-59
% WWeek, 0-6, 0 is Sunday
% WWeek of the year 00-53
% YYear 0000-9999
%% Percent sign

Date, time, datetime, and julianday Functions

Date (date and time string, modifier, modifier ,......)EquivalentStrftime ("% Y-% m-% d ",...)
Time (date and time string, modifier, modifier ,......)Equivalent to strftime ("% H: % M: % S ",...) 
Datetime (date and time string, modifier, modifier ,......) EquivalentStrftime ("% Y-% m-% d % H: % M: % S ",...) 
Julianday (date and time string, modifier, modifier ,......) Equivalent to strftime ("% J ",...)

Date and Time string
You can use the following formats:
The format has strict requirements: 03:35:28 date can only be separated by '-', time can only be separated by ':', less than two digits must be zero

• 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 the fifth to seventh formats (ISO8601), "T" is a character that separates the date and time;
  The eighth to tenth formats only represent the time on January 1,
  In the first format, 'Now 'indicates that the current date and time are returned, and Greenwich Mean Time (UTC) is used );
  The second format represents a Julian Day Numbers.
  
  Modifier
  
  You can use the following modifier to change the date or time:
  
  
• NNN days
• NNN hours
• NNN minutes
• NNN. NNNN seconds
• NNN months
• NNN years
• Start of month
• Start of year
• Start of week
• Start of day
• Weekday N
• Unixepoch
• Localtime
• Utc
• The first six modifiers are the time and date of adding a specified value. The seventh to tenth modifiers indicate that the start of the current date is returned; the first modifier indicates that the next week is the date and time of N. The second modifier indicates the number of seconds since. The second modifier indicates that the local time is returned.
  
  The following are some examples:
  
  
• Current computer time
• SELECT date ('now ')
• The last day of the current month of the computer
• SELECT date ('now ', 'start of month',' + 1 month', '-1 Day ')
• Calculate the date and time expressed by UNIX timestamp 1092941466
• SELECT datetime ('20140901', 'unixepoch ')
• Calculate the local Date and Time in UNIX timestamp 1092941466
• SELECT datetime ('20140901', 'unixepoch ', 'localtime ')
• Current UNIX timestamp of the computer
• SELECT strftime ('% s', 'Now ')
• How many days do the two dates differ?
• SELECT jolianday ('now ')-jolianday ('2017-12-23 ')
• How many seconds is the difference between two date and time?
• SELECT julianday ('now ') * 86400-julianday ('2017-01-01 02:34:56') * 2004
• Calculate the date of the first Tuesday of March.
• SELECT date ('now ', 'start of year',' + 9 months', 'weekday 2 ');
• Retrieve data later than the current time
• Select * from table where Date Field> datetime ('now ', 'localtime ')
• Compare the specified part of the date. Similarly, use the strftime format to compare the date with the day, week, or year.
• Select * from table where strftime ('% m', Date Field) = strftime (' % m', 'right ')
• The first entry later than the specified time       
• Select title, pubtime from article where pubtime> '2017-06-15 03:35:28 'order by pubtime asc Limit 1 Offset 0
• The first entry earlier than the specified time
• Select title, pubtime from article where pubtime <'2017-06-15 03:35:28 'order by pubtime desc Limit 1 Offset 0
  
  Simple Example:
  SELECT
     Datetime (CHANGE_DATE, 'localtime '),
     Strftime ('% Y-% m-% d', CHANGE_DATE, 'localtime '),
     Datetime ('now ', 'localtime '),
     Strftime ('% Y-% m-% d', 'Now', 'localtime '),
     DATE ('now ', 'localtime '),
     Time ('now ', 'localtime '),
     Time ('2017-11-27 01:12:21 ', 'localtime','-8 hour') as Time
  FROM SALARY_HISTORY;
  
  SELECT * FROM SALARY_HISTORY WHERE date (CHANGE_DATE, 'localtime') = Date ('now ', 'localtime ')