SQLite Time Processing

 SQLite database processing time problem and date time function

first of all, the SQLite database in time processing and SQL Server also has a different oracle, the following according to their own examples summarized. A log data table was 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 normal SQL Server query statement is as follows: SELECT * from Log wherelogtime= ' 2011-09-02 ' can query the "' 2011-09-02" data.

in the SQLite database, write the above statement, query No data, indicating that SQL Server in SQLite for time processing is different. So how do you write a SQL statement that takes time as a query condition to query the desired results in the SQLite database? Please see: :::

SELECT * from Log where datetime(logtime) =datetime (' 2011-08-18 16:38:32.000 ')

sqlite data query statement, you must convert time fields and incoming time parameters. That is, add a datetime () conversion.execute the above statement, you can get the results to find. If you follow the SQL Server notation, you will not get the results of the query. In addition, some of the wrong query statements are as follows:

1:select * from Log where datetime (logtime) =datetime (' 2011-08-18 '), this statement can only query one data, that is, the corresponding logid=5, and will not be logid= Data for 1 and logid=2. As canbe seen, SQLite is very strict in time . The accuracy is very high.

2:select * Fromlog where datetime (logtime) =datetime (' 2011-8-18 '), this statement is not any result of the query because SQLite's time requirement isYYYY-MM-DDOrYYYY-MM-DD Hh:mm:ssOf The number of the month is less than 10, then it must be written in the form of 0x (such as: 05), so in the development of the time must be in the storage of the appropriate processing.

3:select *from Log where datetime (logtime) =datetime (' 2011-08-9 '), similarly, this statement does not query any results, you should change the 9 to 09, you can get the query results.

SQLite Date-time function
SQLite does not have a datatime field type, but it can store time in a String type field and provides some useful date-time action functions

Strftime (Date time format, datetime string, modifier, modifier, ...)
Strftime (Date time format, datetime string) is also equivalent to Aauto:
Time (DateTime string, DateTime format), and the formatting syntax used by SQLite and Aauto.
Reference: http://www.aau.cn/doc/reference/libraries/kernel/time/time.html

The strftime () function returns a formatted datetime.
It can be formatted with the following symbols for the date and time:

%d January the day of the first 01-31
%f decimal form of the second, SS. SSSS
%H hours 00-24
%j Day of the year 01-366
%J Julian Day Numbers
%m Month 01-12
%M min 00-59
%s The number of seconds to calculate from the date of 1970-01-01
%s seconds 00-59
%w Week, 0-6,0 is Sunday
%W Week of the year 00-53
%Y year 0000-9999
Percent%%

Date,time,datetime,julianday function

Date (datetime string, modifier, modifier, ...) Equivalent to Strftime ("%y-%m-%d",...)
Time (DateTime string, modifier, modifier, ...) Equivalent to Strftime ("%h:%m:%s",...)
DateTime (Date time string, modifier, modifier, ...) equivalent to strftime ("%y-%m-%d%h:%m:%s",...)
Julianday (Date time string, modifier, modifier, ...) equivalent to strftime ("%J",...)

Date Time string
You can use the following formats:
Format has strict requirements 2008-06-15 03:35:28 date can only be separated by '-', time can only be separated by ': ',less than two digits must be 0

• 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
  The "T" in the fifth to seventh format (ISO8601) is a character that splits the date and time;
  The eighth to the tenth format represents only the time of 2000-01-01,
  The 11th format of ' Now ' represents the return of a current date and time, using GMT (UTC);
  The 12th format represents a Julian day Numbers.
  
  Modifier: Date and time 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
• The first six modifiers of UTC are simply the time and date to increment the specified value, the seventh to tenth modifier indicates the start of the current date, and the 11th modifier indicates the date and time that the next one weeks is N, and the 12th modifier indicates the number of seconds since the beginning of 1970-01-01. The 13th modifier indicates the return of local time.
  
  Here are some examples:
• Computer Current time selectdate (' Now ')
• The last day of the current month of the computer Select Date (' Now ', ' start of Month ', ' +1 month ', '-1 days ')
• Calculates the date and time that Unix timestamp 1092941466 represents the SELECT datetime (' 1092941466 ', ' Unixepoch ')
• Calculates the local date and time of the UNIX timestamp 1092941466 for SELECT datetime (' 1092941466 ', ' Unixepoch ', ' localtime ')
• Computer current UNIX timestamp SELECT strftime ('%s ', ' Now ')
• How many days difference between two dates select Jolianday (' Now ')-jolianday (' 1981-12-23 ')
• Two date time difference how many seconds select Julianday (' Now ') *86400-julianday (' 2004-01-01 02:34:56 ') *86400
• Calculate the date of the first Tuesday of this year in October SELECT date (' Now ', ' Start by year ', ' +9 months ', ' weekday 2 ');
• Take data greater than present time select * from table where date field >datetime (' Now ', ' localtime ')
• Compare date specified partial, extrapolate, also use strftime format date to compare days, weeks, and years select * from table where strftime ('%m ', date field) =strftime ('%m ', ' Now ')
• First select Title,pubtime greater than specified time from article where pubtime> ' 2008-06-15 03:35:28 ' by pubtime ASC Limit 1 OFF Set 0
• The first select Title,pubtime from article where pubtime< ' 2008-06-15 03:35:28 ' is less than the specified time 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 (' 2010-11-27 01:12:21 ', ' localtime ', '-8 hour ') as time
  From Salary_history;
  
  SELECT * from Salary_history wheredate (change_date, ' localtime ') =date (' Now ', ' localtime ')

Save and query for SQLite time

Time saving
   after Google found most of the workarounds for DateTime.ToString ("s") to solve, after testing this method although the problem solved, but not perfect.

Because the time it takes to format it when viewed with the tool SQLite Developer looks strange and not intuitive. And if you manually modify the time in the SQLite Developer

, you will get an error in the program, because the time saved in the format has found a change. Tested to find DateTime. ToString ("Yyyy-mm-dd

Hh:mm:ss") is a good solution to this problem.

Two-time query
   If you use SQLite for development, there must be time for the query, will let you move a lot of brain essence. Because it is not the same as other databases, just as you want to query 2009.3.20

11:00:00 How many people get paid for the SQL, you will write:

   Select COUNT (*) from T where statue= ' 1 ' and [date]= ' 2009-03-2011:00:00 '

Look at the problem, because there is no result, there is a result in the actual table, this is why, in fact, I do not have to figure out. This problem is still found in a foreign

Forum to find a solution. Just change the above statement to

 select count (*) from T where statue= ' 1 ' anddatetime ([date] =datetime (' 2009-03-20 11:00:00 ')

 or
 
  SELECT COUNT (*) from T where statue= ' 1 ' and datetime ([date]) = ' 2009-03-2011:00:00 '

  Remember 2009-03-20 cannot be written as 2009-3-20.


  Above methods after the test has not found the problem, of course, I am also the first to use SQLite to develop a small project, perhaps there are problems not found out, please advise!

Sqlite Query Time format processing

There is no Strptime,sqlite query timestamp field in Windows after the char*, to turn into time_t very uncomfortable. The interim solution is as follows:

1. The SQL statement needs to format the timestamp field:
String strSQL = "Select Strftime ('%%y%%m%%d%%h%%m%%s ', mytime) as Mytimefrom mytable";

2. Write a C function to convert:
Long timestamp2time_t (char* psztimestamp/* "YYYYMMDDHHMMSS" */)
{
int ttt[6][4]={{0,5,4,1900}
, {4,4,2,1}
, {6,3,2,0}
, {8,2,2,0}
, {10,1,2,0}
, {12,0,2,0}};

time_t t=time (NULL); struct tm* Mytm = localtime (&t);
for (int i=0;i<6;i++)
{
Char tmp[5]= "";
memcpy (TMP, ((char*) psztimestamp) +ttt[i][0],ttt[i][2]);
* ((((int*) Mytm) +ttt[i][1]) =atoi (TMP)-ttt[i][3];
}

Return Mktime (MYTM);
}

      

SQLite supports 5 date and time functions as follows:

S.N.	Function	Example
1	Date (timestring, modifiers ...)	This returns the date in this FORMAT:YYYY-MM-DD
2	Time (timestring, modifiers ...)	This returns the time as HH:MM:SS
3	DateTime (timestring, modifiers ...)	This returns YYYY-MM-DD HH:MM:SS
4	Julianday (timestring, modifiers ...)	This returns the number of days since noon in Greenwich on November, 4714 B.C.
5	Strftime (timestring, modifiers ...)	This returns the date formatted according to the format string specified as the first argument formatted as per formatters Explained below.

The five date and time functions of the time string as parameters. A time string followed by 0 or more modifiers. The Strftime () function also requires a format string as its first argument. The following sections will give you detailed time strings and different types of modifiers.

5.1 Time string:

A time string can be used in any of the following formats:

S.N.	Time string	Example
1	Yyyy-mm-dd	2010-12-30
2	YYYY-MM-DD hh:mm	2010-12-30 12:10
3	Yyyy-mm-dd HH:MM:SS. Sss	2010-12-30 12:10:04.100
4	MM-DD-YYYY hh:mm	30-12-2010 12:10
5	hh:mm	12:10
6	Yyyy-mm-ddThh:mm	2010-12-30 12:10
7	HH:MM:SS	12:10:01
8	YYYYMMDD HHMMSS	20101230 121001
9	Now	2013-05-07

You can use "T" as a literal character to separate the date and time.

5.2 Modifiers

The subsequent time string can be changed by 0 or more modifiers that will change the date and/or any of the above five functions to return time. Modifiers are applied to the left-to-right and below modifiers for use in SQLite:

• NNN days
• NNN hours
• NNN minutes
• NNN. NNNN seconds
• NNN months
• NNN years
• Start of month
• Start of year
• Start of day
• Weekday N
• Unixepoch
• LocalTime
• Utc

5.3 Formatting:

SQLite provides a very handy function strftime () to format any date and time. You can use the following to replace the formatted date and time:

Alternative	Describe
%d	Day of month, 01-31
%f	Fractional seconds, SS. Sss
%H	Hour, 00-23
%j	Day of the year, 001-366
%J	Julian Day number, DDDD. DDDD
%m	Month, 00-12
%M	Minute, 00-59
%s	Seconds since 1970-01-01
%s	Seconds, 00-59
%w	Day of Week, 0-6 (0 is Sunday)
%W	Week of year, 01-53
%Y	Year, YYYY
%%	% symbol

5.4 Examples

Let's try different examples and now use SQLite hints. The current date is calculated as follows:

sqlite> SELECT Date (' Now ');

2013-05-07

The following calculates the last day of the current month:

sqlite> SELECT Date (' Now ', ' start of Month ', ' +1 month ', '-1 day ');

2013-05-31

The following calculates UNIX timestamp 1092941466 for a given date and time:

Sqlite> SELECT datetime (1092941466, ' Unixepoch ');

2004-08-19 18:51:06

The following calculation of Unix timestamp 1092941466 offsets the date and time of the local time zone:

Sqlite> SELECT datetime (1092941466, ' Unixepoch ', ' localtime ');

2004-08-19 11:51:06

The following calculates the current UNIX timestamp:

Sqlite> SELECT datetime (1092941466, ' Unixepoch ', ' localtime ');

1367926057

Number of days since the United States "declaration of Independence" was signed in the following calculations:

sqlite> SELECT julianday (' Now ')-julianday (' 1776-07-04 ');

86504.4775830326

The following is a special moment calculated in seconds since 2004:

sqlite> SELECT strftime ('%s ', ' Now ')-strftime ('%s ', ' 2004-01-01 02:34:56 ');

295001572

The following calculation date is the first Tuesday of the year October:

sqlite> SELECT Date (' Now ', ' start of Year ', ' +9 months ', ' weekday 2 ');

2013-10-01

The following calculation time is from Unix epoch seconds (similar to strftime ('%s ', ' Now '), in addition to the decimal part):

Sqlite> SELECT (Julianday (' Now ')-2440587.5) *86400.0;

1367926077.12598

Convert between UTC and local time values, format datetime, and modify it using UTC or LocalTime as follows:

Sqlite> SELECT time (' localtime ');

05:00:00

Sqlite> SELECT time (' 12:00pm ', ' UTC ');

19:00:00

SQLite Time Processing