Working with Date with SQLite and Python

SQLite does not have datatypes to store date or time. Instead it provides builtin functions that are capable of storing dates and times as TEXT, REAL or INTEGER values.

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")
• REAL as Julian day numbers
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC

Date and Time Functions

SQLite supports five date and time functions as follows:

1. date(time-value, modifier, modifier, ...)

2. time(time-value, modifier, modifier, ...)

3. datetime(time-value, modifier, modifier, ...)

4. julianday(time-value, modifier, modifier, ...)

5. strftime(format, time-value, modifier, modifier, ...)

Refer to following for more details: https://sqlite.org/lang_datefunc.html

Python code to Store, Retrieve and Manipulate DateTime in SQLite

SQLite3 comes bundled with Python 3 so we can directly start using it.

import sqlite3
conn = sqlite3.connect('my.db')
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS expense (name TEXT, date TEXT, time TEXT)")
cur.execute("INSERT INTO expense VALUES ('Grocery', date('now'), time('now'))")
cur.execute("SELECT * FROM expense WHERE date > '2021-02-19')
conn.commit()
conn.close()