Sqlite: CURRENT_TIMESTAMP 是 GMT，不是机器的时区 [英] Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine

问题描述

我有一个带有此列定义的 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');

这看起来不符合我的需要，所以我试着稍微改变了datetime"函数，结果是这样的:

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?

这篇关于Sqlite: CURRENT_TIMESTAMP 是 GMT，不是机器的时区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！