Sqlite:CURRENT_TIMESTAMP 是 GMT，不是机器的时区

2022-01-13 00:00:00 timestamp sql timezone sqlite

我有一个带有此列定义的 sqlite (v3) 表:

I have a sqlite (v3) table with this column definition:

"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP

此数据库所在的服务器位于 CST 时区.当我在不包含时间戳列的情况下插入表时，sqlite 会自动使用 GMT 中的当前时间戳填充该字段，而不是 CST.

The server that this database lives on is in the CST time zone. When I insert into my table without including the timestamp column, sqlite automatically populates that field with the current timestamp in GMT, not CST.

有没有办法修改我的插入语句以强制存储的时间戳在 CST 中?另一方面，最好将它存储在 GMT 中(例如，以防数据库移动到不同的时区)，所以有没有办法可以修改我的选择 SQL 以将存储的时间戳转换为 CST从表中提取它?

Is there a way to modify my insert statement to force the stored timestamp to be in CST? On the other hand, it is probably better to store it in GMT (in case the database gets moved to a different timezone, for example), so is there a way I can modify my select SQL to convert the stored timestamp to CST when I extract it from the table?

推荐答案

我在 sqlite 文档上找到了 (https://www.sqlite.org/lang_datefunc.html) 这段文字:

I found on the sqlite documentation (https://www.sqlite.org/lang_datefunc.html) this text:

计算给定 unix 的日期和时间时间戳1092941466，并进行补偿为您当地的时区.

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

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

这看起来不符合我的需要，所以我尝试稍微更改日期时间"功能，结果如下:

That didn't look like it fit my needs, so I tried changing the "datetime" function around a bit, and wound up with this:

select datetime(timestamp, 'localtime')

这似乎可行 - 这是为您的时区转换的正确方法，还是有更好的方法来做到这一点?

That seems to work - is that the correct way to convert for your timezone, or is there a better way to do this?

相关文章