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 froYYYY-MM-DD HH:MM:SS.SSS and it should be in UTC timezone.

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

Most importanly – 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.