SQL Sqlite: CURRENT_TIMESTAMP 是 GMT，不是机器的时区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/381371/
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: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
提问by BrianH
I have a sqlite (v3) table with this column definition:
我有一个带有此列定义的 sqlite (v3) 表：
"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP
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 时区。当我在不包含时间戳列的情况下插入我的表时，sqlite 会自动使用 GMT 中的当前时间戳而不是 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?
有没有办法修改我的插入语句以强制存储的时间戳在 CST 中？另一方面，最好将它存储在 GMT 中（例如，如果数据库被移动到不同的时区），那么有没有一种方法可以修改我的选择 SQL 以在我将存储的时间戳转换为 CST 时从表中提取它？
回答by BrianH
I found on the sqlite documentation (https://www.sqlite.org/lang_datefunc.html) this text:
我在 sqlite 文档（https://www.sqlite.org/lang_datefunc.html）上找到了这段文字：
Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.
计算给定 unix 时间戳 1092941466 的日期和时间，并补偿您的本地时区。
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:
这看起来不符合我的需要，所以我尝试稍微改变“datetime”函数，最后得到了这个：
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?
这似乎有效 - 这是为您的时区转换的正确方法，还是有更好的方法来做到这一点？
回答by hoju
simply use local time as the default:
只需使用本地时间作为默认值：
CREATE TABLE whatever(
....
timestamp DATE DEFAULT (datetime('now','localtime')),
...
);
回答by Roger Lipscombe
You should, as a rule, leave timestamps in the database in GMT, and only convert them to/from local time on input/output, when you can convert them to the user's (not server's) local timestamp.
通常，您应该在数据库中以 GMT 格式保留时间戳，并且仅在输入/输出时将它们转换为本地时间/从本地时间转换，当您可以将它们转换为用户（而非服务器）的本地时间戳时。
It would be nice if you could do the following:
如果您可以执行以下操作，那就太好了：
SELECT DATETIME(col, 'PDT')
...to output the timestamp for a user on Pacific Daylight Time. Unfortunately, that doesn't work. According to this SQLite tutorial, however (scroll down to "Other Date and Time Commands"), you can ask for the time, and then apply an offset (in hours) at the same time. So, if you do know the user's timezone offset, you're good.
...输出太平洋夏令时用户的时间戳。不幸的是，这行不通。但是，根据此 SQLite 教程（向下滚动到“其他日期和时间命令”），您可以询问时间，然后同时应用偏移量（以小时为单位）。因此，如果您确实知道用户的时区偏移量，那就太好了。
Doesn't deal with daylight saving rules, though...
不处理夏令时规则，虽然......
回答by polyglot
In the (admitted rare) case that a local datatime is wanted (I, for example, store local time in one of my database since all I care is what time in the day is was and I don't keep track of where I was in term of time zones...), you can define the column as
在需要本地数据时间的（公认的罕见）情况下（例如，我将本地时间存储在我的一个数据库中，因为我只关心一天中的什么时间，而我不跟踪我在哪里在时区方面......），您可以将列定义为
"timestamp" TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M','now', 'localtime'))
The %Y-%m-%dT%H:%M part is of course optional; it is just how I like my time to be stored. [Also, if my impression is correct, there is no "DATETIME" datatype in sqlite, so it does not really matter whether TEXT or DATETIME is used as data type in column declaration.]
%Y-%m-%dT%H:%M 部分当然是可选的；这就是我喜欢我的时间被存储的方式。[另外，如果我的印象是正确的，sqlite 中没有“DATETIME”数据类型，所以在列声明中使用 TEXT 还是 DATETIME 作为数据类型并不重要。]
回答by polyglot
When having a column defined with "NOT NULL DEFAULT CURRENT_TIMESTAMP," inserted records will always get set with UTC/GMT time.
当使用“ NOT NULL DEFAULT CURRENT_TIMESTAMP”定义列时，插入的记录将始终设置为 UTC/GMT 时间。
Here's what I did to avoid having to include the time in my INSERT/UPDATE statements:
以下是我为避免在 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
Test to see if it works...
测试看看它是否有效...
--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
Hope that helps.
希望有帮助。
回答by liquide
SELECT datetime('now', 'localtime');
回答by Jens A. Koch
SELECT datetime(CURRENT_TIMESTAMP, 'localtime')
SELECT datetime(CURRENT_TIMESTAMP, 'localtime')
回答by danger89
You can also just convert the time column to a timestamp by using strftime():
您也可以使用 strftime() 将时间列转换为时间戳：
SELECT strftime('%s', timestamp) as timestamp FROM ... ;
Gives you:
给你：
1454521888
1454521888
'timestamp' table column can be a text field even, using the current_timestampas DEFAULT.
'timestamp' 表列甚至可以是文本字段，使用current_timestampas DEFAULT。
Without strftime:
没有 strftime：
SELECT timestamp FROM ... ;
Gives you:
给你：
2016-02-03 17:51:28
2016-02-03 17:51:28
回答by danger89
I think this might help.
我认为这可能会有所帮助。
SELECT datetime(strftime('%s','now'), 'unixepoch', 'localtime');
回答by Joseph
The current time, in your machine's timezone:
当前时间，在您机器的时区：
select time(time(), 'localtime');
