SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as INTEGER, TEXT or REAL values:
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
Integer is best if you will want to quickly search based on datetime values, etc.
Adding a column used for DateTime to a table
CREATE TABLE IF NOT EXISTS tbl1(id int primary key, CreatedDateTime INTEGER);Adding a DateTime value to a column being used for DateTime
db1cursor.execute("""INSERT INTO my_table(
CreatedDateTime,
CameraId
) VALUES (
datetime('now', 'localtime'),
1
)""");
# datetime('now') <<<Use for UTC
# datetime('now','localtime'); <<<Use for local time
USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.