A summary of the use of datetime functions in SQLite3 _sqlite

Copy Code code as follows:

Import Sqlite3
conn = Sqlite3.connect ('/tmp/sqlite.db ')
cur = conn.cursor ()

What's next? Build a chart. It should be noted here that SQLite does not support creating indexes while creating tables, so take two steps to create a table and then create an index

Copy Code code as follows:

create_table_stmt = ' CREATE table IF not EXISTS test_table (
ID INTEGER PRIMARY KEY autoincrement,
Duration INTEGER,
Event_date TEXT,
Parameter TEXT);

Create_index = ' CREATE index IF not EXISTS idx_id on test_table (ID); '
Cur.execute (CREATE_TABLE_STMT)
Cur.execute (Create_index)
Conn.commit ()

and insert a little bit of data into it, SQLite only supports 5 basic data types

Copy Code code as follows:

Null. The value is a NULL value
INTEGER. The value is a signed integer, stored at 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value
Real. The value is a floating point value, stored as a 8-byte IEEE floating point number
TEXT. The value is a text string, stored using the database encoding (UTF-8, utf-16be or Utf-16le)
Blob. The value is a blob of data, stored exactly as it was input

The question is, where is the sqlite time and date type? The original SQLite can save time and date in a few data types

Copy Code code as follows:

TEXT as ISO8601 strings (' Yyyy-mm-dd HH:MM:SS. SSS ').
Real as Julian day numbers, the number of days since noon into Greenwich on November-4714 to the B.C C Gregorian calendar.
INTEGER as Unix time, the number of seconds since 1970-01-01 00:00:00 UTC.

insert_stmt = ' INSERT into test_table values (?,?,?) '
Record = (123, ' 2011-11-30 12:34:56 ', ' Hello World ')
Cur.execute (insert_stmt, record)
Conn.commit ()

When you save a date as a string, you cannot directly take it out for the immediate day, and then call the SQLite date function before using it.
For example, find the data that was stored the day before:

Copy Code code as follows:

SELECT
Id
Duration
Event_date,
Parameter
From test_table
WHERE
Date (event_date) = Date (' Now ', '-1 day ', ' localtime ')
Order by ID, event_date

View table Structure SELECT * FROM Sqlite_master
View table Information PRAGMA table_info (table_name)

Time and date functions in SQLite

SQLite contains the following time/date functions:

Copy Code code as follows:

DateTime () ......... ........ Date and time of generation
Date () ............ ........ Date of production
Time () .................. Generation time
Strftime () ............... Format the date and time produced by the above three functions

DateTime () is used: datetime (Date/time, modifier, modifier ...)
The syntax of date () and time () is the same as DateTime ().

In a time/Date function, you can use a string in the following format as an argument:

Copy Code code as follows:

Yyyy-mm-dd
YYYY-MM-DD hh:mm
YYYY-MM-DD HH:MM:SS
hh:mm
HH:MM:SS
Now it's time to produce the present.

For example (the time to write this note is October 17, 2006 from 8 o'clock in the evening to 10, Beijing time):

Copy Code code as follows:

Select DateTime (' Now ');
Results: 2006-10-17 12:55:54

Select DateTime (' 2006-10-17 ');
Results: 2006-10-17 12:00:00

Select DateTime (' 2006-10-17 00:20:00 ', ' +1 hour ', ' -12 minute ');
Results: 2006-10-17 01:08:00

Select Date (' 2006-10-17 ', ' +1 Day ', ' +1 Year ');
Results: 2007-10-18

Select DateTime (' Now ', ' start of Year ');
Results: 2006-01-01 00:00:00

Select DateTime (' Now ', ' start of Month ');
Results: 2006-10-01 00:00:00

Select DateTime (' Now ', ' start of ');
Results: 2006-10-17 00:00:00

# Although the 2nd parameter was added 10 hours, it was set to zero by the 3rd parameter of start of day to 00:00:00
# The subsequent 4th parameter, based on 00:00:00, increased the time by 10 hours into 10:00:00.
Select DateTime (' Now ', ' +10 hour ', ' start of day ', ' +10 Hour ');
Results: 2006-10-17 10:00:00

# Convert Greenwich time zone to cost time zone.
Select DateTime (' Now ', ' localtime ');
Results: 2006-10-17 21:21:47

Select DateTime (' Now ', ' +8 Hour ');
Results: 2006-10-17 21:24:45

The strftime () function converts a date string in the YYYY-MM-DD HH:MM:SS format to another form of string.
The syntax of Strftime () is strftime (format, date/time, modifier, modifier, ...)

It can format dates and times with the following symbols:
%d months, 01-31
%f decimal form of the second, SS. Sss
%H hours, 00-23
%j calculated that one day was the first of the year, 001-366
%m month, 00-12
%m minutes, 00-59
%s number of seconds from January 1, 1970 to today
%s seconds, 00-59
%w Week, 0-6 (0 is Sunday)
%w the first few weeks of the year, 01-53
%Y years, YYYY
Percent percent%

Examples of the use of strftime () are as follows:

Copy Code code as follows:

Select Strftime ('%y/%m/%d%h:%m:%s ', ' Now ', ' localtime ');

Results: 2006/10/17 21:41:09