sqlitetimestamptimestamp-with-timezone

How to autogenerate insert timestamp with millisecond precision in UTC format in SQLite

I have the following table

CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, insertTimestamp <- )

For the following column insertTimestamp I want to generate timestamp in this format: YYYY-MM-DD HH:MM:SS.SSS and it should be in UTC time zone.

How do I do it? What is the most efficient way of doing this?

Most importantly - If my server has incorrect time, will it mess up my database time?

Solution

  • You should use the function strftime() function with '%Y-%m-%d %H:%M:%f' format and the 'now' modifier which will return the current datetime with milliseconds in UTC:

    CREATE TABLE IF NOT EXISTS user (
  id INTEGER PRIMARY KEY, 
  insertTimestamp TEXT NOT NULL DEFAULT(strftime('%Y-%m-%d %H:%M:%f', 'now')) 
);

    See the demo.

    SQLite is serverless, so it will return the current datetime from your system and not any server.