Sqlite: текущая метка времени находится в GMT, а не в часовом поясе машины

У меня есть таблица sqlite (v3) с этим определением столбца:

"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP

сервер, на котором находится эта база данных, находится в часовом поясе CST. Когда я вставляю в свою таблицу без включения столбца timestamp, sqlite автоматически заполняет это поле текущей меткой времени в GMT, а не CST.

есть ли способ изменить мой оператор insert, чтобы заставить сохраненную метку времени быть в CST? С другой стороны, вероятно, лучше хранить его в GMT (в случае, если например, база данных перемещается в другой часовой пояс), поэтому есть ли способ изменить мой select SQL для преобразования сохраненной метки времени в CST, когда я извлекаю ее из таблицы?

11 102
sql timestamp sqlite timezone

11 ответов:

Я нашел в документации sqlite (https://www.sqlite.org/lang_datefunc.html) этот текст:

вычислить дату и время, заданные unix метка времени 1092941466, и компенсировать для вашего местного часового пояса.

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

это не похоже, что это соответствует моим потребностям, поэтому я попытался немного изменить функцию "datetime" и закончил с этим:

select datetime(timestamp, 'localtime')

это, кажется, работает - это правильный способ конвертировать для вашего часовой пояс, или есть лучший способ сделать это?

119

просто используйте местное время по умолчанию:

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

вы должны, как правило, оставлять метки времени в базе данных в GMT и преобразовывать их только в/из локального времени на входе/выходе, когда вы можете преобразовать их в локальную метку времени пользователя (а не сервера).

было бы неплохо, если бы вы могли сделать следующее:

SELECT DATETIME(col, 'PDT')

...чтобы вывести метку времени для пользователя на Тихоокеанском дневном времени. К сожалению, это не работает. Согласно это SQLite учебник, однако (прокрутите вниз до "другие Дата и время Команды"), вы можете задать время, а затем применить смещение (в часах), в то же время. Итак, если вы знаете смещение часового пояса пользователя, Вы хороши.

не имеет дело с правилами перехода на летнее время, хотя...

13

в (допустимом редком) случае, когда требуется локальный datatime (я, например, храню местное время в одной из своих баз данных, так как все, что меня волнует, это то, что время в день было, и я не отслеживаю, где я был в терминах часовых поясов...), вы можете определить столбец как 

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

%Y - %m - %dT%H:%M часть, конечно, необязательно; это просто, как мне нравится мое время, чтобы быть сохранены. [Кроме того, если мое впечатление верно, в sqlite нет типа данных "DATETIME", поэтому это не имеет особого значения используется ли текст или DATETIME в качестве типа данных в объявлении столбца.]

12

при наличии столбца, определенного с помощью "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

надеюсь, что это поможет.

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

Я думаю, что это может помочь. 

SELECT datetime(strftime('%s','now'), 'unixepoch', 'localtime');
3

SELECT datetime(CURRENT_TIMESTAMP, 'localtime')

3

вы также можете просто преобразовать столбец времени в метку времени с помощью strftime ():

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

дает вам:

1454521888

столбец таблицы "отметка времени" может быть даже текстовым полем, используя current_timestamp по умолчанию.

без strftime:

SELECT timestamp FROM ... ;

дает вам:

2016-02-03 17:51:28

3

текущее время, в часовом поясе вашей машины:

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

по состоянию на http://www.sqlite.org/lang_datefunc.html

2

Time ( 'now', 'localtime' ) и дата ( 'now', 'localtime' ) строительство.

1