Sqlite: CURRENT_TIMESTAMP是基于GMT而非机器时区的

Question

我有一个sqlite（v3）表，其列定义如下：

"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP

这个数据库所在的服务器位于CST时区。如果我插入表格时没有包含时间戳列，SQLite会自动使用当前GMT时间戳填充该字段，而不是CST时间戳。

是否有一种方法可以修改我的插入语句，以强制存储的时间戳为CST？另一方面，最好将其存储在GMT时间戳中（例如，万一数据库被移动到不同的时区），那么是否有一种方法可以修改我的select SQL，在从表格中提取它时将存储的时间戳转换为CST？

- BrianH

---

将时间戳存储为协调世界时 (UTC) 被认为是最佳实践。在展示时间戳时，将其转换为本地时间。 - csl

POSIX将时间戳定义为协调世界时（UTC）：https://dev59.com/bE3Sa4cB1Zd3GeqPydb0#34107910 - Ciro Santilli OurBigBook.com

不确定其他类型的数据库是否会面临这种问题。你可能想象，在日本创建数据库后，可以在英国使用数据！ - NoChance

Answer 1

我在sqlite文档（https://www.sqlite.org/lang_datefunc.html）中找到了以下内容：

计算出 unix 时间戳 1092941466 对应的日期和时间，并进行本地时区的调整。

SELECT datetime(1092941466, 'unixepoch', 'localtime');

那似乎不符合我的需求，所以我尝试对“datetime”函数进行一些更改，结果得到了这个：

select datetime(timestamp, 'localtime')

看起来这个方法可行 - 这是将时间转换为你的时区的正确方式吗，还是有更好的方法可以做到这一点？

- BrianH

Answer 2

默认情况下，只需使用本地时间：

CREATE TABLE whatever(
     ....
     timestamp DATE DEFAULT (datetime('now','localtime')),
     ...
);

- hoju

---

但这会存在跨时区不可传递的问题，对吗？ - Vadim Peretokin

是的，我也遇到了同样的问题，我在ZF2中使用SQLite。 - Rohutech

@xFighter_我知道我的应用程序永远不需要其他方式……直到你忘记了或有人正在使用它。 - MKesper

请注意 - SQLite3默认将DateTime值存储为简单的整数Unix时间戳，没有附加时区信息。如果与本地时间一起使用，sqlite将仅从该数字中添加/减去，并有效地保存错误的时间戳（因为它不会表示自unix时间开始以来的秒数） - 并且连接到具有其他区域设置的客户端将显示错误时间，并且没有简单的方法进行更正。 - Falco

Answer 3

一般来说，应该将时间戳存储在数据库中的时区设置为GMT，在输入/输出时仅在需要转换为用户（而不是服务器）本地时间戳时进行转换。

如果可能的话，最好执行以下操作：

SELECT DATETIME(col, 'PDT')

想要输出太平洋夏令时间下的用户时间戳，但可惜这并不可行。然而根据这个SQLite教程（向下滚动至“其他日期和时间命令”），你可以请求时间，然后同时应用一个偏移量（以小时为单位）。因此，如果你知道用户所在的时区偏移量，那就没问题了。

不过这种方法无法处理夏令时规则......

- Roger Lipscombe

Answer 4

在极少数情况下，可能需要使用本地时间（例如，我将本地时间存储在数据库中，因为我只关心这一天的时间，而不关心以何种时区的方式追踪我的位置...），您可以将列定义为：

"timestamp" TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M','now', 'localtime'))

%Y-%m-%dT%H:%M部分当然是可选的；这只是我喜欢存储时间的方式。[而且，如果我的印象没错，sqlite中没有“DATETIME”数据类型，所以在列声明中使用TEXT或DATETIME作为数据类型并不重要。]

- polyglot

---

SQLite中的本地时间在时间戳和用户不通过BI工具直接使用数据的应用程序中可能不太重要。然而，在存储所有应用程序特定日期（例如生日）时，本地时间是您需要使用的。 - NoChance

如果您使用DATETIME，Sqlite将在内部将该值存储为整数，这可能会对查询性能、行大小和缓存产生重大影响，特别是当一个日期时间值是64位整数而不是20字节字符串时。 - Falco

Answer 5

当使用"NOT NULL DEFAULT CURRENT_TIMESTAMP"定义列时，插入的记录将始终设置为UTC/GMT时间。

以下是我避免在INSERT/UPDATE语句中包含时间的方法：

--Create a table having a CURRENT_TIMESTAMP:
CREATE TABLE FOOBAR (
    RECORD_NO INTEGER NOT NULL,
    TO_STORE INTEGER,
    UPC CHAR(30),
    QTY DECIMAL(15,4),
    EID CHAR(16),
    RECORD_TIME NOT NULL DEFAULT CURRENT_TIMESTAMP)

--Create before update and after insert triggers:
CREATE TRIGGER UPDATE_FOOBAR BEFORE UPDATE ON FOOBAR
    BEGIN
       UPDATE FOOBAR SET record_time = datetime('now', 'localtime')
       WHERE rowid = new.rowid;
    END

CREATE TRIGGER INSERT_FOOBAR AFTER INSERT ON FOOBAR
    BEGIN
       UPDATE FOOBAR SET record_time = datetime('now', 'localtime')
       WHERE rowid = new.rowid;
    END

测试一下是否可行...

--INSERT a couple records into the table:
INSERT INTO foobar (RECORD_NO, TO_STORE, UPC, PRICE, EID)
    VALUES (0, 1, 'xyz1', 31, '777')

INSERT INTO foobar (RECORD_NO, TO_STORE, UPC, PRICE, EID)
    VALUES (1, 1, 'xyz2', 32, '777')

--UPDATE one of the records:
UPDATE foobar SET price = 29 WHERE upc = 'xyz2'

--Check the results:
SELECT * FROM foobar

希望这有所帮助。

- Patrick

---

这与用户hoju提供的答案不同吗？CREATE TABLE whatever( .... timestamp DATE DEFAULT (datetime('now','localtime')), . );？ - NoChance

虽然这不是最干净的方法，但它能够正常工作。它可以正确调整时间，并且由于我只在较小的变更队列中使用sqlite，所以这种方式违反了规则也没关系。 - Jan S.

虽然这不是最干净的方法，但它能够正常工作。它能正确调整时间，而且由于我只在较小的变更队列中使用sqlite，所以这种方式违反了也没关系。 - undefined

Answer 6

SELECT datetime(CURRENT_TIMESTAMP, 'localtime')

---

简而言之：CURRENT_TIMESTAMP是UTC时间，并转换为本地时间。

“localtime”修饰符（21）假设其左侧的时间值为协调世界时（UTC），并调整该时间值以使其成为本地时间。如果“localtime”后跟的时间不是UTC时间，则行为是未定义的。“utc”修饰符是“localtime”的相反。“utc”假设其左侧的时间值为本地时区，并调整该时间值以使其成为UTC时间。如果左侧的时间不是本地时间，则“utc”的结果是未定义的。--来源：https://www.sqlite.org/lang_datefunc.html#modifiers

- Jens A. Koch

Answer 7

SELECT datetime('now', 'localtime');

- liquide

Answer 8

Time ('now', 'localtime')和Date('now', 'localtime')是有效的。

- Julian

Answer 9

您可以使用strftime()将时间列转换为时间戳：

SELECT strftime('%s', timestamp) as timestamp FROM ... ;

给你：

1454521888

'timestamp' 表列甚至可以是文本字段，使用 current_timestamp 作为默认值。

没有 strftime：

SELECT timestamp FROM ... ;

给你：

2016年02月03日17:51:28

- Melroy van den Berg

Answer 10

您机器所在时区的当前时间：

select time(time(), 'localtime');

根据 {{link1}}。

- Joseph