SQLite Date and Time

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:

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)
Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Database and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s