HOWTO · SQLite

How to Get Automatic Timestamp in SQLite

In this article we discuss how to get the automatic timestamp in SQLite.

On this page

SQLite can add a creation time automatically with a column declared DEFAULT CURRENT_TIMESTAMP when an insert omits that column. If the column is explicitly supplied as NULL, the default does not apply; with a NOT NULL constraint, that insert fails. SQLite has no dedicated date/time storage class, so choose a consistent text, integer, or real representation. In Python, sqlite3 returns a datetime object only when conversion is configured.

Add an Automatic Timestamp to a SQLite Table

Declare a timestamp column with DEFAULT CURRENT_TIMESTAMP, for example: CREATE TABLE events (id INTEGER PRIMARY KEY, name TEXT NOT NULL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP);. An insert such as INSERT INTO events (name) VALUES ('launch'); omits created_at, so SQLite uses the default. If an insert explicitly names created_at and supplies NULL, the default is not invoked; the NOT NULL constraint rejects the insert. Use the same table and column names in later queries, such as SELECT id, name, created_at FROM events;.

CURRENT_TIMESTAMP is an SQLite keyword that supplies the current UTC date and time as text in YYYY-MM-DD HH:MM:SS form. A default applies to inserted rows; it does not automatically change when another column is updated. If you need an update time, set it in the update statement or use a trigger designed for that behavior.

Choose a Date and Time Representation in SQLite

SQLite values use the storage classes TEXT, INTEGER, and REAL (as well as NULL and BLOB). A declared name such as DATETIME or TIMESTAMP does not create a native date/time storage type. SQLite’s type affinity rules still govern how values are stored, so pick a representation and use it consistently.

Text is readable and works well for dates formatted from most significant to least significant components, such as YYYY-MM-DD HH:MM:SS.SSS. Integers can store Unix time in seconds, while real numbers can store Julian day values. SQLite’s date/time functions can interpret these representations. DATE_ADD and UNIX_TIMESTAMP are not SQLite functions; use SQLite’s date(), time(), and datetime() functions for supported conversions.

Retrieve SQLite Timestamps as Python datetime Objects

The sqlite3 module returns ordinary SQLite values as Python values according to their storage class; a timestamp-looking string is normally returned as str. To retrieve a Python datetime, register a converter and enable declared-type parsing. This standalone example creates an in-memory database, inserts a fixed timestamp, and prints the converted value and its type.

This self-contained example configures conversion and demonstrates that the query returns the timestamp as a Python datetime object:

import datetime
import sqlite3


def convert_timestamp(raw_value):
    return datetime.datetime.fromisoformat(raw_value.decode("utf-8"))


sqlite3.register_converter("timestamp", convert_timestamp)
connection = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
connection.execute("CREATE TABLE events (created_at TIMESTAMP NOT NULL)")
connection.execute("INSERT INTO events VALUES (?)", ("2024-02-03 04:05:06",))
created_at = connection.execute("SELECT created_at FROM events").fetchone()[0]
print(created_at.isoformat(sep=" "))
print(type(created_at).__name__)
connection.close()
2024-02-03 04:05:06
datetime

Conversion is a Python-side behavior configured for the connection; SQLite itself still stores a value according to its storage rules. If a converter is not registered or parsing is not enabled, select the value as text and parse it in application code when needed.

Convert Unix Timestamps to Dates and Times in SQLite

Pass an integer Unix timestamp and the unixepoch modifier to SQLite’s date/time functions. For a fixed value, datetime(1793956207, 'unixepoch') returns a date and time, date(1793956207, 'unixepoch') returns the date, and time(1793956207, 'unixepoch') returns the time of day. These expressions use the same instant and differ only in which part they return. A current-time expression would vary with when it is evaluated, so use a fixed input when a repeatable example is needed.

Store Date and Time Values as Text

A text representation such as 2019-02-02 11:21:08.321 follows the YYYY-MM-DD HH:MM:SS.SSS pattern. It is a formatting convention stored with SQLite’s TEXT storage class, not a special date/time type. Keep the format, precision, and timezone convention consistent across rows. Consistently formatted values with the larger date components first can be compared lexically in chronological order; mixing formats or timezone conventions can make text comparisons misleading.