英文:
How to autogenerate insert timestamp with millisecond precision in UTC format in SQLite
问题
我有以下的表格
CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY, insertTimestamp <- )
对于列 insertTimestamp，我想以这种格式生成时间戳：YYYY-MM-DD HH:MM:SS.SSS，并且应该是在UTC时区。
如何做到这一点？最高效的方法是什么？
最重要的是 - 如果我的服务器时间不准确，会影响我的数据库时间吗？
英文:
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?
答案1
得分: 2
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.<br/>
SQLite is serverless, so it will return the current datetime from your system and not any server.
英文:
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.<br/>
SQLite is serverless, so it will return the current datetime from your system and not any server.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论