Comparación de date y hora de SQLite

Parece que no puedo get resultados fiables de la consulta en una database sqlite usando una cadena de date y hora como comparación:

select * from table_1 where mydate >= '1/1/2009' and mydate <= '5/5/2009' 

¿cómo debo manejar las comparaciones de date y hora con sqlite?

actualización: campo mydate es un tipo de datos DateTime

Solución:

siguiendo la function de date y hora y teniendo un formatting de cadena como AAAA-MM-DD HH: mm: ss logré buenos resultados de la siguiente manera

 select * from table_1 where mydate >= Datetime('2009-11-13 00:00:00') and mydate <= Datetime('2009-11-15 00:00:00') 

SQLite no tiene types de date y hora dedicados, pero tiene algunas funciones de date y hora . Siga los formattings de representación de cadenas (en realidad solo los formattings 1-10) entendidos por esas funciones (almacenando el valor como una cadena) y luego puede usarlos, además la comparación lexicográfica en las cadenas coincidirá con la comparación de date y hora (siempre que no lo haga) intente comparar las dates con las veces o las dates con las veces, lo cual no tiene mucho sentido de todos modos).

Dependiendo del idioma que use, incluso puede get una conversión automática . (Lo cual no se aplica a las comparaciones en sentencias SQL como en el ejemplo, pero hará su vida más fácil).

Para resolver este problema, guardo las dates como YYYYMMDD . Por lo tanto, where mydate >= '20090101' and mydate <= '20050505'

Simplemente funciona todo el time. Puede que solo necesite escribir un analizador para controlar cómo los usuarios pueden ingresar sus dates para poder convertirlas a YYYYMMDD .

Tuve el mismo problema recientemente y lo resolví así:

 SELECT * FROM table WHERE strftime('%s', date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date) 

Lo siguiente está funcionando bien para mí usando SQLite:

 SELECT * FROM ingresosgastos WHERE datelogging BETWEEN "2010-01-01" AND "2013-01-01" 

Sqlite no se puede comparar en las dates. tenemos que convertir en segundos y convertirlo en un número integer.

Ejemplo

 SELECT * FROM Table WHERE CAST(strftime('%s', date_field) AS integer) <=CAST(strftime('%s', '2015-01-01') AS integer) ; 

Lo siguiente funcionó para mí.

 SELECT * FROM table_log WHERE DATE(start_time) <= '2017-01-09' AND DATE(start_time) >= '2016-12-21' 

También puede escribir sus propias funciones de usuario para manejar las dates en el formatting que elija. SQLite tiene un método bastante simple para escribir sus propias funciones de usuario. Por ejemplo, escribí algunas para agregar duraciones de time juntas.

Tuve que almacenar la hora con la información de la zona horaria y pude get las consultas que funcionan con el siguiente formatting:

 "SELECT * FROM events WHERE datetime(date_added) BETWEEN datetime('2015-03-06 20:11:00 -04:00') AND datetime('2015-03-06 20:13:00 -04:00')" 

La hora se almacena en la database como TEXTO regular en el siguiente formatting:

 2015-03-06 20:12:15 -04:00 

A continuación se muestran los methods para comparar las dates, pero antes tenemos que identificar el formatting de date almacenado en DB

Tengo las dates almacenadas en formatting MM / DD / AAAA HH: MM, por lo que deben compararse en ese formatting

  1. La consulta siguiente compara la conversión de la date en formatting MM / DD / YYY y obtiene datos de los últimos cinco días hasta hoy. BETWEEN operador ayudará y usted puede simplemente especificar la date de inicio Y la date de finalización.

     select * from myTable where myColumn BETWEEN strftime('%m/%d/%Y %H:%M', datetime('now','localtime'), '-5 day') AND strftime('%m/%d/%Y %H:%M',datetime('now','localtime')); 
  2. Debajo de la consulta usará mayor que el operador (>).

      select * from myTable where myColumn > strftime('%m/%d/%Y %H:%M', datetime('now','localtime'), '-5 day'); 

Todo el cálculo que he hecho está usando la hora actual, puede cambiar el formatting y la date según su necesidad.

Espero que esto te ayudará

Summved

Mi consulta lo hice de la siguiente manera:

 SELECT COUNT(carSold) FROM cars_sales_tbl WHERE date BETWEEN '2015-04-01' AND '2015-04-30' AND carType = "Hybrid" 

Tengo la pista por la respuesta de @ ifnetworkingy. Todo lo que hice fue que quería que esta consulta se ejecutara en iOS usando Objective-C. ¡Y funciona!

¡Espero que alguien que desarrolle iOS también se beneficie de esta respuesta!

Tengo una situación en la que quiero datos desde hace hasta dos días y hasta el final de hoy. Llegué a lo siguiente.

 WHERE dateTimeRecorded between date('now', 'start of day','-2 days') and date('now', 'start of day', '+1 day') 

Vale, técnicamente también salgo a medianoche mañana como el póster original, si hubo datos, pero mis datos son históricos.

La key para recordar es que el póster inicial excluyó todos los datos después del 2009-11-15 00:00:00. Por lo tanto, se incluyeron todos los datos que se registraron a la medianoche del día 15, pero cualquier dato después de la medianoche del día 15 no lo fue. Si su consulta fue,

 select * from table_1 where mydate between Datetime('2009-11-13 00:00:00') and Datetime('2009-11-15 23:59:59') 

Uso de la cláusula between para mayor claridad.

Hubiera sido un poco mejor. Todavía no toma en count los segundos intercalares en los que una hora puede tener más de 60 segundos, pero lo suficientemente buena para las discusiones aquí 🙂