SQL sqlite 数据库默认时间值“现在”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/200309/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sqlite database default time value 'now'
提问by Joep
Is it possible in a sqlite database to craete a table that has a timestamp column that default to DATETIME('now')?
是否可以在 sqlite 数据库中创建一个具有默认时间戳列的表DATETIME('now')？
Like this:
像这样：
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
t TIMESTAMP DEFAULT DATETIME('now')
);
This gives an error... How to resolve?
这给出了一个错误...如何解决？
回答by Owen
回答by rev
according to dr. hipp in a recent list post:
根据博士。hipp 在最近的列表帖子中：
CREATE TABLE whatever(
....
timestamp DATE DEFAULT (datetime('now','localtime')),
...
);
回答by Adam Luter
It's just a syntax error, you need parenthesis: (DATETIME('now'))
这只是一个语法错误，你需要括号： (DATETIME('now'))
If you look at the documentation, you'll note the parenthesis that is added around the 'expr' option in the syntax.
如果您查看文档，您会注意到在语法中的 'expr' 选项周围添加了括号。
回答by user272735
This is a full example based on the other answers and comments to the question. In the example the timestamp (created_at-column) is saved as unix epochUTC timezone and converted to local timezone only when necessary.
这是一个基于其他答案和问题评论的完整示例。在示例中，时间戳 ( created_at-column) 保存为unix epochUTC 时区，并仅在必要时转换为本地时区。
Using unix epoch saves storage space - 4 bytes integer vs. 24 bytes string when stored as ISO8601 string, see datatypes. If 4 bytes is not enough that can be increased to 6 or 8 bytes.
使用 unix epoch 可以节省存储空间 - 当存储为 ISO8601 字符串时，4 字节整数与 24 字节字符串，请参阅数据类型。如果 4 个字节不够，可以增加到 6 个或 8 个字节。
Saving timestamp on UTC timezone makes it convenient to show a reasonable value on multiple timezones.
在 UTC 时区保存时间戳可以方便地在多个时区显示合理的值。
SQLite version is 3.8.6 that ships with Ubuntu LTS 14.04.
SQLite 版本是 3.8.6，随 Ubuntu LTS 14.04 一起提供。
$ sqlite3 so.db
SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
sqlite> .headers on
create table if not exists example (
id integer primary key autoincrement
,data text not null unique
,created_at integer(4) not null default (strftime('%s','now'))
);
insert into example(data) values
('foo')
,('bar')
;
select
id
,data
,created_at as epoch
,datetime(created_at, 'unixepoch') as utc
,datetime(created_at, 'unixepoch', 'localtime') as localtime
from example
order by id
;
id|data|epoch |utc |localtime
1 |foo |1412097842|2014-09-30 17:24:02|2014-09-30 20:24:02
2 |bar |1412097842|2014-09-30 17:24:02|2014-09-30 20:24:02
Localtime is correct as I'm located at UTC+2 DST at the moment of the query.
本地时间是正确的，因为我在查询时位于 UTC+2 DST。
回答by Nianliang
It may be better to use REAL type, to save storage space.
最好使用 REAL 类型，以节省存储空间。
Quote from 1.2 section of Datatypes In SQLite Version 3
引自SQLite 版本 3中数据类型的1.2 部分
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values
SQLite 没有专门用于存储日期和/或时间的存储类。相反，SQLite 的内置日期和时间函数能够将日期和时间存储为 TEXT、REAL 或 INTEGER 值
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
t REAL DEFAULT (datetime('now', 'localtime'))
);
see column-constraint .
请参阅列约束。
And inserta row without providing any value.
并插入一行而不提供任何值。
INSERT INTO "test" DEFAULT VALUES;
回答by Sandeep
It is syntax error because you did not write parenthesis
这是语法错误，因为你没有写括号
if you write
如果你写
Select datetime('now') then it will give you utc time but if you this write it query then you must add parenthesis before this so (datetime('now')) for UTC Time. for local time same Select datetime('now','localtime') for query
选择 datetime('now') 然后它会给你 UTC 时间，但是如果你写它查询，那么你必须在这之前添加括号，所以 (datetime('now')) UTC 时间。对于本地时间相同选择 datetime('now','localtime') 进行查询
(datetime('now','localtime'))
（日期时间（'现在'，'本地时间'））
回答by Bilbo
This alternative example stores the local time as Integer to save the 20 bytes. The work is done in the field default, Update-trigger, and View. strftime must use '%s' (single-quotes) because "%s" (double-quotes) threw a 'Not Constant' error on me.
此替代示例将本地时间存储为 Integer 以节省 20 个字节。该工作在字段 default、Update-trigger 和 View 中完成。strftime 必须使用“%s”（单引号），因为“%s”（双引号）向我抛出“非常量”错误。
Create Table Demo (
idDemo Integer Not Null Primary Key AutoIncrement
,DemoValue Text Not Null Unique
,DatTimIns Integer(4) Not Null Default (strftime('%s', DateTime('Now', 'localtime'))) -- get Now/UTC, convert to local, convert to string/Unix Time, store as Integer(4)
,DatTimUpd Integer(4) Null
);
Create Trigger trgDemoUpd After Update On Demo Begin
Update Demo Set
DatTimUpd = strftime('%s', DateTime('Now', 'localtime')) -- same as DatTimIns
Where idDemo = new.idDemo;
End;
Create View If Not Exists vewDemo As Select -- convert Unix-Times to DateTimes so not every single query needs to do so
idDemo
,DemoValue
,DateTime(DatTimIns, 'unixepoch') As DatTimIns -- convert Integer(4) (treating it as Unix-Time)
,DateTime(DatTimUpd, 'unixepoch') As DatTimUpd -- to YYYY-MM-DD HH:MM:SS
From Demo;
Insert Into Demo (DemoValue) Values ('One'); -- activate the field Default
-- WAIT a few seconds --
Insert Into Demo (DemoValue) Values ('Two'); -- same thing but with
Insert Into Demo (DemoValue) Values ('Thr'); -- later time values
Update Demo Set DemoValue = DemoValue || ' Upd' Where idDemo = 1; -- activate the Update-trigger
Select * From Demo; -- display raw audit values
idDemo DemoValue DatTimIns DatTimUpd
------ --------- ---------- ----------
1 One Upd 1560024902 1560024944
2 Two 1560024944
3 Thr 1560024944
Select * From vewDemo; -- display automatic audit values
idDemo DemoValue DatTimIns DatTimUpd
------ --------- ------------------- -------------------
1 One Upd 2019-06-08 20:15:02 2019-06-08 20:15:44
2 Two 2019-06-08 20:15:44
3 Thr 2019-06-08 20:15:44
