How to use 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.
There are articles on the Internet that translate the official SQLite WIKI content into Chinese. If you are interested, please search. Here I will simply record some of my frequently used content.

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.

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
1 sqlite> select julianday ('right')-julianday ('2017-12-22 '); 2 16.2240774538368 3 4 sqlite> select (julianday ('right')-julianday ('2017-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.

The code is as follows:
1 sqlite> select strftime ('% Y-% m-% d', '2017-01-10 '); 2 2013-01-10 3 4 sqlite> select strftime ('% Y-% m-% d', '2017-1-1 '); 5 6 sqlite> select strftime ('% Y-% m-% d', '2014, 1, 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:

 

The code is as follows:
1 conn = sqlite3.connect (db) 2 c = conn. cursor () 3 rows = c.exe cute ("select distinct (datetime (julianday (ins_time) FROM items order by ins_time desc limit 0, 1 ") 4 for row in rows: 5 result = 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:

The code is as follows:
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:

The code is as follows:
Start of year Start of month Start of week Start of day

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