Search code examples
sqlsqlite

SQLite Current Timestamp with Milliseconds?

I am storing a timestamp field in a SQLite3 column as TIMESTAMP DATETIME DEFAULT CURRENT_TIMESTAMP and I was wondering if there was any way for it to include milliseconds in the timestamp as well?

Solution

  • Update[2025-01-20]

    Modern versions of sqlite allow a subsec modifier for the datetime(), time(), and unixepoch() functions. Thus, OP could now do this:

    TIMESTAMP DATETIME DEFAULT(datetime('subsec'))

    Or for an integer column named "createdAt" with default unix epoch as integer milliseconds:

    createdAt  INTEGER DEFAULT(unixepoch('subsec') * 1000)

    [Original answer]

    Instead of CURRENT_TIMESTAMP, use (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')) so that your column definition become:

    TIMESTAMP DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))

    For example:

    CREATE TABLE IF NOT EXISTS event
(when_ts DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')));