Comparación de fecha y hora de SQLite

1. Compare dos fechas con el tipo de datos DateTime
2. Utilice la función strftime() para comparar dos fechas en SQLite

La base de datos SQLite utiliza diferentes tipos de datos para almacenar, incluidos números enteros, cadenas y algunos formatos de fecha y hora. Para el almacenamiento de fecha y hora, hay varios tipos, como fecha, pequeña fecha y hora, DateTime, datetime2, etc.

Generalmente, usamos la cláusula WHERE para comparar en la base de datos SQLite y recuperar los datos requeridos.

La pregunta es cómo comparar diferentes tipos de formatos de fecha y hora dentro de la base de datos SQL. Recuerde, no hay una forma directa de comparar las fechas y horas.

Compare dos fechas con el tipo de datos DateTime

En SQLite, no existe tal forma de comparar fechas directamente. Sin embargo, hay funciones integradas para fechas y horas en SQLite, que pueden almacenar fechas y horas en el texto, números enteros o valores reales.

Dependen de la aplicación y podemos elegir el formato para las funciones de fecha y hora integradas.

1. El texto toma las cadenas ISO8601 con el formato YYYY-MM-DD HH:MM:SS.SSS.
2. Integer toma la hora Unix, que tiene el formato 1970-01-01 00:00:00 UTC.

Entonces, para comparar, generalmente convertimos la fecha y la hora en una cadena en un formato particular para que sea consistente. Por ejemplo, almacenamos la fecha en el formato YYYY-MM-DD hh:mm:ss.

Si almacenamos constantemente la fecha y la hora en este formato, podemos comparar fácilmente la fecha y la hora. Podemos seguir la declaración a continuación para demostrar un ejemplo de comparación simple con fechas.

SELECT * FROM name_of_the_table WHERE comparsion_date < '2020-04-05 00:00:00';

Aunque podemos ver que la comparación no es numérica, tenemos una comparación alfabética. Esto es correcto si el formato de fecha almacenado y la fecha de comparación tienen formatos similares.

Aquí, debemos recordar dos puntos importantes.

1. Se sugiere almacenar la hora en formato de 24 horas ya que nos será fácil compararlas.
2. No es necesario almacenar la fecha y la hora en una sola columna, pero el factor importante es la consistencia en el formato. Porque si las fechas y las horas están en columnas separadas, entonces el formato debe ser YYYY-MM-DD para la columna de fecha y hh:mm:ss para la columna de hora.

Entonces, si la fecha y la hora están en columnas separadas, escribimos la consulta SQL de la siguiente manera. El punto clave es hacer que el formato de fecha y hora sea consistente en toda la base de datos.

SELECT * FROM name_of_the_table WHERE comparsion_date = '2020-05-03' AND comparison_time = '08:03:11';

Otra razón por la que se prefiere el formato, YYYY-MM-DD hh:mm:ss, es porque cuando ordenamos del índice más alto al más bajo, es decir, de horas a segundos, la comparación es muy fácil y el rendimiento gana. no ser degradado.

Utilice la función strftime() para comparar dos fechas en SQLite

En SQLite, usamos la función strftime() para devolver la fecha en un formato específico proporcionado al argumento. El segundo argumento devuelve la cadena de tiempo en un formato específico; el resto son modificadores para obtener datos con resultados diferentes.

La sintaxis de la función strftime() es la siguiente.

strftime(date_format, time_string, remaining_modifiers, ...)

Para comparar la fecha y la hora usando la función strftime(), usamos los operadores BETWEEN y AND en la instrucción SQL para encontrar una fecha particular en un período específico.

SELECT * FROM name_of_the_table WHERE
    strftime('%s', c_date) BETWEEN strftime('%s', start_date) AND strftime('%s', end_date)

Usando la función strftime(), podemos generar la diferencia entre dos fechas y horas desde la hora actual hasta una fecha y hora especificadas.

SELECT strftime('%s','now') - strftime('%s','2019-09-05 03:52:35');

Producción :

Output of strftime('%s','now') - strftime('%s','2019-09-05 03:52:35')
-----------------------------------------------------------
2010032

Convierta DateTime a horas, minutos, segundos y milisegundos

Usamos la función strftime() para convertir la unidad DateTime a horas, minutos, segundos y milisegundos. Necesitamos usar la función strftime() para lograr los siguientes resultados.

SELECT strftime('%H %M %S %s','now');

Producción :

Output of strftime('%H %M %S %s','now')
-----------------------------
16 54 04 2541572981

Calcule los segundos desde una hora específica hasta la hora actual (ahora)

También usamos la función strftime() para convertir el tipo de datos DateTime a segundos y calcular el total de segundos restantes de los argumentos proporcionados a la hora actual en segundos.

SELECT strftime('%s','now') - strftime('%s','2019-01-04 01:25:47');

Producción :

Output of strftime('%s','now') - strftime('%s','2019-01-04 01:25:47')
-----------------------------------------------------------
3221184

Calcule el historial de tiempo en años, meses y días

Tome un ejemplo simple del historial de mantenimiento del trabajo donde hay una tabla con la fecha de inicio y finalización del trabajo, y el usuario desea calcular el historial de tiempo total en términos de años, meses y días.

Aquí está el formato de la tabla con tres columnas: emp_id, start_job_date y end_job_date. El tipo de dato de emp_id es entero mientras que el resto de las columnas, start_job_date y end_job_date, son DateTime.

Para calcular la diferencia entre start_job_date y end_job_date de la tabla anterior usando las consultas SQL, usamos la función strftime() para tener directamente la diferencia en años, meses y días.

SELECT strftime('%Y',end_job_date - start_job_date) as "Difference in years",
strftime('%m',end_job_date - start_job_date) as "Difference in months",
strftime('%d',end_date - start_job_date) as "Difference in days"
FROM job_history;

Producción: