Use of the date and time functions of Python and SQLite

The time function of SQLite is slightly different from the time function of Python, so we will record it for future query.
The official SQLite wiki content is translated into Chinese on the Internet.ArticleIf you are interested, you can search for some of my frequently-used content here.

Five time functions of SQLite:

Date (date and time string, modifier, modifier ,......)
Time (date and time string, modifier, modifier ,......)
Datetime (date and time string, modifier, modifier ,......)
Julianday (date and time string, modifier, modifier ,......)
Strftime (date and time format, date and time string, modifier, modifier ,......)

 

Date () returns a date in YYYY-MM-DD format;
Time () returns a date time in "YYYY-MM-DD hh: mm: SS" format;
Datetime () returns an object in the datetime format;
Julianday () returns the number of days, starting from January 1, November 24, 4714 BC, Greenwich Mean Time;
Strftime () returns a formatted date and time.

 

Example 1: Calculate the current time
It is worth noting that it is best to add 'localtime'. Only one 'now 'returns the Greenwich Mean Time. Otherwise, the time difference will be eight hours if I am in the GMT + 8. I mistakenly believe that the time of the VM is not synchronized.

 1 SQLite >  Select   Datetime ( '  Now  '  );  2   2013  -  01  -  07   05 : 21 : 07 3   4 SQLite >   Select   Datetime ( '  Now  ' , '  Localtime  '  );  5   2013  -  01 -  07   13 : 21 : 09 

 

Example 2: Calculate the last day of the current month
You can add or subtract the year, month, and day in the modifier, but the week is not supported. The returned result is null.

 1 SQLite >   Select Date ( '  Now  ' , '  Start of month ' , '  + 1 month  ' , '  -1 day  '  );  2   2013  -  01  -  31  3   4 SQLite>   Select Date ( '  Now  ' , '  Start of month  ' , '  + 1 month  ' , '  -1 day  ' , '  + 1 year '  );  5   2014  -  01  -  31  6   7 SQLite >   Select Date ( '  Now  ' , ' Start of month  ' , '  + 1 month  ' , '  -1 day  ' , '  + 1 week  ' );

 

Example 3: calculate the number of days for the two dates
Of course, if we calculate the hour, minute, or second of the difference, we can multiply the values of 12, 60, and 60 respectively to achieve the effect. For example, the following calculation is the hour of the difference between the two dates.

 1 SQLite >   Select Julianday ( '  Now  ' ) - Julianday ( '  2012-12-22  '  );  2   16.2240774538368  3   4 SQLite >   Select (Julianday ( ' Now  ' ) - Julianday ( '  2012-12-22  ' )) *   12  ;  5   194.796493470669 

 

Example 4: Use strftime to format the date of a string
Make sure that the input string is in the same format as the date and time.

 1 SQLite >   Select Strftime ( '  % Y-% m-% d  ' , '  2013-01-10  '  );  2   2013  -  01  -  10  3   4 SQLite >  Select Strftime ( '  % Y-% m-% d  ' , '  2013-1-1-1  '  );  5   6 SQLite >   Select Strftime ( '  % Y-% m-% d  ' , ' Maid, 1  ' );

Use the datetime object obtained from SQLite in Python
For Python Date and Time Functions, you can search for related articles or directly view Python instructions. Here we will only briefly mention what operations are supported by datetime objects in Python.

Datetime2 = Datetime1 + Timedelta
Datetime2 = Datetime1 - Timedelta
Timedelta = Datetime1 - Datetime2
Datetime1 < Datetime2
Add or subtract values on the datetime object, but note that it must be a timedelta object.

Finally, data of the datetime type may be obtained from SQLite in daily use, but the data obtained from SQLite will be a STR object in Python.
Therefore, the strptime of the datetime module must be used for conversion:

1Conn =Sqlite3.connect (db)2C =Conn. cursor ()3Rows = c.exe cute ("Select distinct (datetime (julianday (ins_time) from items order by ins_time DESC limit 0, 1")4 ForRowInRows:5Result = datetime. strptime (row [0],"% Y-% m-% d % H: % m: % s")
 

 

Refer:

Strftime () can be formatted using the following symbols:
% D the number of 0-31 in a month
% F seconds in decimal form SS. ssss
% H hour 00-24
% J day of the year 01-366
% J Julian day number
% M month 01-12
% M minutes 00-59
% S number of seconds calculated from January 1
% S seconds 00-59
% W week 0-6, 0 represents Sunday
% W week of the year 00-53
% Y year 0000-9999
% Percent

 

Combined with the preceding formatting symbol, you can use strftime () to represent several other functions:

Date (...) --> strftime ("% Y-% m-% d ",...)
Time (...) --> strftime ("% H: % m: % s ",...)
Datetime (...) --> strftime ("% Y-% m-% d % H: % m: % s ",...)
Julianday (...) --> strftime ("% J ",...)

 

Several modifiers for time initialization:

Start of year
Start of month
Start of week
Start of day

The above corrections initialize the year, month, week, and day.