SQLite is a wonderful open source file-based database system useful for similar tasks as Access file-based databases on Microsoft Windows systems. The popularity of SQLite is partially due to the widely available libraries and tools which ship with most Linux systems, are built into Python, or are available as free downloads. The core SQLite tools are licensed as Public Domain for the better good of computer users worldwide. Do not confuse SQLite with a full relational database management system. You will not find a database server to manage concurrent connections and transactions. What is available is a limited relational feature set well suited to single-user and single-process basic database tasks.
For quick automation tasks and single-user testing and development, SQLite may provide just the functionality you’re looking for. It is very convenient because there is no database server to set up or configure. You can even create all your tables in-memory if you do not need a file on-disk to save your data. A SQLite file may be wise to use if your data set is larger than you want in RAM.
Enough SQLite background, let’s jump into a common problem SQLite users must deal with. There is no built-in data type for date and/or time fields. To solve this issue, a set of date and time functions is provided as a supported workaround. I like to use the C (Unix/Posix) timestamp, seconds since the epoch of Jan 1, 1970 – stored as a REAL or INTEGER data type. Here is an example to query a timestamp using the DATETIME function:
SELECT col1name, col2name, DATETIME(datecolname, 'unixepoch', 'localtime') AS datecol FROM mytablename ORDER BY datecolname ;
Related SQLite resources:
- Modern releases of Python include built-in SQLite support
- SQLite Manager is an excellent SQLite GUI (built on Firefox framework)
- A Command-Line Shell “sqlite3” is included with the official SQLite software bundle
Here are some notes on useful features of the “sqlite3” database shell tool:
.mode column # output formatted columns .header on # show headers in output .table # show tables .schema TABLENAME # show definition for table .exit # close the sqlite3 tool (quit)