type - sqlite date format dd/mm/yyyy

Cómo comparar los valores sqlite TIMESTAMP

Question

Tengo una base de datos Sqlite en la que quiero seleccionar filas cuyo valor en una columna TIMESTAMP es antes de una fecha determinada. Creo que esto es simple, pero no puedo hacerlo. He intentado esto:

SELECT * FROM logged_event WHERE logged_event.CREATED_AT < ''2010-05-28 16:20:55''

y varias variaciones en él, como con las funciones de fecha. He leído http://sqlite.org/lang_datefunc.html y http://www.sqlite.org/datatypes.html y espero que la columna sea de tipo numérico y que la comparación se haga en el valor de la marca de tiempo de Unix. Aparentemente no. ¿Alguien que pueda ayudar? Si es importante, estoy probando esto en Sqlite Expert Personal.

Editar:

Aquí hay una descripción de la tabla tipo:

CREATE TABLE [logged_event]
(
[id] INTEGER  NOT NULL PRIMARY KEY,
[created_at] TIMESTAMP,
[name] VARCHAR(64),
[data] VARCHAR(512)
);

Y los datos de prueba:

INSERT INTO table VALUES(1,''2010-05-28T15:36:56+0200'',''test'',''test'');
INSERT INTO table VALUES(2,''2010-05-28T16:20:49+0200'',''test'',''test'');
INSERT INTO table VALUES(3,''2010-05-28T16:20:51+0200'',''test'',''test'');
INSERT INTO table VALUES(4,''2010-05-28T16:20:52+0200'',''test'',''test'');
INSERT INTO table VALUES(5,''2010-05-28T16:20:53+0200'',''test'',''test'');
INSERT INTO table VALUES(6,''2010-05-28T16:20:55+0200'',''test'',''test'');
INSERT INTO table VALUES(7,''2010-05-28T16:20:57+0200'',''test'',''test'');

Answer 1

El problema es con la forma en que ha insertado los datos en su tabla: la sintaxis +0200 no coincide con ninguno de los formatos de tiempo de SQLite :

1. AAAA-MM-DD
2. AAAA-MM-DD HH: MM
3. AAAA-MM-DD HH: MM: SS
4. AAAA-MM-DD HH: MM: SS.SSS
5. YYYY-MM-DDTHH: MM
6. YYYY-MM-DDTHH: MM: SS
7. YYYY-MM-DDTHH: MM: SS.SSS
8. HH: MM
9. HH: MM: SS
10. HH: MM: SS.SSS
11. ahora
12. DDDDDDDDDD

Cambiarlo para usar el formato SS.SSS funciona correctamente:

sqlite> CREATE TABLE Foo (created_at TIMESTAMP);
sqlite> INSERT INTO Foo VALUES(''2010-05-28T15:36:56+0200'');
sqlite> SELECT * FROM Foo WHERE foo.created_at < ''2010-05-28 16:20:55'';
sqlite> SELECT * FROM Foo WHERE DATETIME(foo.created_at) < ''2010-05-28 16:20:55'';
sqlite> INSERT INTO Foo VALUES(''2010-05-28T15:36:56.200'');
sqlite> SELECT * FROM Foo WHERE DATETIME(foo.created_at) < ''2010-05-28 16:20:55'';
2010-05-28T15:36:56.200

Si no puede cambiar el formato cuando se inserta, puede que tenga que volver a hacer algo "inteligente" y modificar la cadena real (es decir, para reemplazar el + con a . , Etc.).

(respuesta original)

No ha descrito qué tipo de datos contiene su columna CREATED_AT . Si es realmente una fecha y hora, se comparará correctamente contra una cadena:

sqlite> SELECT DATETIME(''now'');
2010-05-28 16:33:10
sqlite> SELECT DATETIME(''now'') < ''2011-01-01 00:00:00'';
1

Si se almacena como una marca de tiempo de Unix, debe llamar a la función DATETIME con el segundo argumento como ''unixepoch'' para compararlo con una cadena:

sqlite> SELECT DATETIME(0, ''unixepoch'');
1970-01-01 00:00:00
sqlite> SELECT DATETIME(0, ''unixepoch'') < ''2010-01-01 00:00:00'';
1
sqlite> SELECT DATETIME(0, ''unixepoch'') == DATETIME(''1970-01-01 00:00:00'');
1

Si ninguno de los dos resuelve su problema (¡e incluso si lo hacen!) Siempre debe publicar algunos datos para que otras personas puedan reproducir su problema. Incluso debe sentirse libre de encontrar un subconjunto de sus datos originales que aún reproduzca el problema.

Answer 2

El soporte de SQLite para tipos de fecha / hora es muy limitado. Es posible que deba usar su propio método para mantener la información del tiempo. Al menos, eso es lo que hice.

Puede definir sus propias funciones almacenadas para hacer comparaciones utilizando la API SQLite create_function ().

Answer 3

Lo mejor que puedo decir es que es completamente razonable incluir un especificador de zona horaria; vea el texto "formatos 2 a 10 puede ..." en http://www.sqlite.org/lang_datefunc.html Sin embargo, el problema es que solo las funciones de fecha interpretan las marcas de tiempo como fechas. Por lo tanto, para una comparación real, debe pasar la marca de tiempo a través de una función de fecha o almacenar cosas tales que la comparación de cadenas funcione. Un enfoque sería codificar su aplicación de manera que llame a datetime en cada valor que inserte y en cada valor literal en una instrucción select. Sin embargo, simplemente no incluir el huso horario sugerido por la respuesta existente puede ser más fácil en muchas aplicaciones.