datepart - Sqlite: CURRENT_TIMESTAMP está en GMT, no en la zona horaria de la máquina

sqlite now datetime

Question

Tengo una tabla sqlite (v3) con esta definición de columna:

"timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP

El servidor en el que vive esta base de datos se encuentra en la zona horaria CST. Cuando inserto en mi tabla sin incluir la columna de marca de tiempo, sqlite completa automáticamente ese campo con la marca de tiempo actual en GMT, no en CST.

¿Hay alguna manera de modificar mi declaración de inserción para forzar que la marca de tiempo almacenada esté en CST? Por otro lado, probablemente sea mejor almacenarlo en GMT (en caso de que la base de datos se mueva a una zona horaria diferente, por ejemplo), así que hay una forma en que puedo modificar mi SQL de selección para convertir la marca de tiempo almacenada a CST cuando extraerlo de la mesa?

Answer 1

Al tener una columna definida con " NOT NULL DEFAULT CURRENT_TIMESTAMP ", los registros insertados siempre se establecerán con la hora UTC / GMT.

Esto es lo que hice para evitar tener que incluir el tiempo en mis instrucciones 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

Prueba para ver si funciona ...

--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

Espero que ayude.

Answer 2

Como regla general, debe dejar marcas de tiempo en la base de datos en GMT y solo convertirlas a / desde hora local en entrada / salida, cuando puede convertirlas a la marca de tiempo local del usuario (no del servidor).

Sería bueno si pudieras hacer lo siguiente:

SELECT DATETIME(col, ''PDT'')

... para mostrar la marca de tiempo de un usuario en Pacific Daylight Time. Lamentablemente, eso no funciona. De acuerdo con este tutorial de SQLite , sin embargo (desplácese hacia abajo a "Otros comandos de fecha y hora"), puede solicitar la hora y luego aplicar un desplazamiento (en horas) al mismo tiempo. Por lo tanto, si conoce el desplazamiento de la zona horaria del usuario, está bien.

No se ocupa de las reglas de ahorro de luz diurna, aunque ...

Answer 3

Creo que esto podría ayudar.

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

Answer 4

En el caso (admitido raro) de que se desea un tiempo de datos local (yo, por ejemplo, almaceno hora local en una de mis bases de datos ya que lo único que me importa es la hora del día y no hago un seguimiento de dónde estaba en términos de zonas horarias ...), puede definir la columna como

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

La parte% Y-% m-% dT% H:% M es, por supuesto, opcional; es justo como me gusta que mi tiempo sea almacenado. [Además, si mi impresión es correcta, no hay ningún tipo de datos "DATETIME" en sqlite, por lo que realmente no importa si TEXT o DATETIME se utilizan como tipo de datos en la declaración de columna.]

Answer 5

Encontré en la documentación de sqlite ( https://www.sqlite.org/lang_datefunc.html ) este texto:

Calcule la fecha y la hora con la marca de tiempo de unix 1092941466 y compense su zona horaria local.

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

No parecía que se ajustara a mis necesidades, así que intenté cambiar la función de "fecha y hora" un poco, y terminé con esto:

select datetime(timestamp, ''localtime'')

Eso parece funcionar: ¿es la forma correcta de convertir para su zona horaria, o hay una mejor manera de hacerlo?

Answer 6

La hora actual, en la zona horaria de su máquina:

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

Según http://www.sqlite.org/lang_datefunc.html

Answer 7

También puede convertir la columna de tiempo a una marca de tiempo utilizando strftime ():

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

Te dio:

1454521888

La columna de la tabla ''timestamp'' puede ser un campo de texto incluso, usando current_timestamp como DEFAULT.

Sin tiempo completo:

SELECT timestamp FROM ... ;

Te dio:

2016-02-03 17:51:28

Answer 8

simplemente use la hora local como valor predeterminado:

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

Answer 9

SELECT datetime(CURRENT_TIMESTAMP, ''localtime'')

Answer 10

Time ( ''now'', ''localtime'' ) y Date ( ''now'', ''localtime'' ) funcionan.

Answer 11

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