Cómo trabajar eficazmente con fechas y horas en MySQL

febrero 11, 2021 Aranzulla de Los Pobres

Las fechas y horas son importantes, ayudan a mantener las cosas organizadas y son un aspecto integral de cualquier operación de software.

Trabajar de manera eficiente con ellos dentro de la base de datos a veces puede parecer confuso, ya sea trabajando en las distintas zonas horarias, agregando / restando fechas y otras operaciones.

Conozca las diversas funciones de MySQL disponibles para manejar y administrar fácilmente fechas / horas dentro de su base de datos.

Trabajar con zonas horarias

Para ayudar a mantener las cosas estandarizadas, solo debe trabajar con fechas / horas en la zona horaria UTC. Cada vez que establece una conexión a la base de datos MySQL, debe cambiar la zona horaria a UTC, lo que se puede hacer con la siguiente declaración SQL:

 SET TIME_ZONE = '+0:00'

Dado que ahora todas las fechas se guardarán en UTC, siempre sabrá con qué está trabajando, lo que hará las cosas más simples y directas.

Cuando sea necesario, puede convertir fácilmente la zona horaria de cualquier valor de fecha / hora / marca de tiempo con la práctica función CONVERT_TZ () MySQL. Primero debe conocer el desplazamiento, por ejemplo, PST en la costa oeste de América del Norte es UTC -08: 00, por lo que podría usar:

 SELECT CONVERT_TZ('2021-02-04 21:47:23', '+0:00', '-8:00');

Esto da como resultado 2021-02-04 13:47:23 que es exactamente correcto. Los tres argumentos pasados a CONVERT_TZ () son primero la fecha y hora / marca de tiempo con la que está comenzando (use ahora () para la hora actual), el segundo siempre será '+0: 00' ya que todas las fechas están forzadas a UTC en la base de datos , y el último es el desplazamiento al que deseamos convertir la fecha.

Agregar / restar fechas

Muchas veces necesita sumar o restar fechas, por ejemplo, si necesita recuperar registros de hace una semana o programar algo dentro de un mes.

Afortunadamente, MySQL tiene las excelentes funciones DATE_ADD () y DATE_SUB () que hacen que esta tarea sea extremadamente fácil. Por ejemplo, puede restar dos semanas de la fecha actual con la declaración SQL:

 SELECT DATE_SUB(now(), interval 2 week);

Si, en cambio, quisiera agregar tres días a una marca de tiempo existente, usaría:

 SELECT DATE_ADD('2021-02-07 11:52:06', interval 3 day);

Ambas funciones funcionan igual, el primer argumento es la marca de tiempo con la que está comenzando y el segundo argumento es el intervalo para sumar o restar. El segundo argumento siempre tiene el mismo formato, comenzando con el intervalo de palabras seguido de un valor numérico y el intervalo en sí, que puede ser cualquiera de los siguientes: segundo, minuto, hora, día, semana, mes, trimestre, año.

Por otro ejemplo, si quisiera recuperar todos los inicios de sesión que ocurrieron en los últimos 34 minutos, podría usar una declaración SQL como:

 SELECT * FROM logins WHERE login_date >= DATE_SUB(now(), interval 45 minute);

Como puede ver, esto recuperaría todos los registros de la tabla de inicios de sesión con una fecha de inicio de sesión mayor que la hora actual menos 45 minutos, o en otras palabras, los últimos 45 minutos.

Obtenga la diferencia entre fechas

A veces necesitas saber cuánto tiempo ha transcurrido entre dos fechas. Puede obtener fácilmente el número de días entre dos fechas diferentes con la función DATEDIFF , como la siguiente declaración SQL:

 SELECT DATEDIFF(now(), '2020-12-15');

La función DATEDIFF toma dos argumentos, ambos son sellos de fecha / hora y da el número de días entre ellos. El ejemplo anterior mostrará el número de días transcurridos desde el 15 de diciembre de 2020 hasta hoy.

Para obtener el número de segundos entre dos fechas, la función TO_SECONDS () puede resultar útil, por ejemplo:

 SELECT TO_SECONDS(now()) - TO_SECONDS('2021-02-05 11:56:41');

Esto resultará en el número de segundos entre las dos fechas proporcionadas.

Extraer segmentos de fechas

Hay varias funciones de MySQL que le permiten extraer fácilmente segmentos específicos de fechas, como si solo quisiera el mes, el día del año o la hora. Aquí hay algunos ejemplos de tales funciones:

 SELECT MONTH('2021-02-11 15:27:52');

 SELECT HOUR(now());

 SELECT DAYOFYEAR('2021-07-15 12:00:00');

Las declaraciones SQL anteriores darían como resultado 02 , la hora actual y 196, ya que el 15 de septiembre es el día 196 del año. Aquí hay una lista de todas las funciones de extracción de fecha disponibles, cada una con solo un argumento, la fecha se extrae de:

 - SECOND()
 - MINUTE()
 - HOUR()
 - DAY()
 - WEEK() - Number 0 - 52 defining the week within the year.
 - MONTH()
 - QUARTER() - Number 1 - 4 defining the quarter of the year.
 - YEAR()
 - DAYOFYEAR() - The day of the year (eg. Sept 15th = 196).
 - LAST_DAY() - The last day in the given month.
 - DATE() - The date in YYYY-MM-DD format without the time.
 - TIME() The time in HH:II:SS format without the date.
 - TO_DAYS() - The number of days since AD 0.
 - TO_SECONDS() - The number of seconds since AD 0.
 - UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

Por ejemplo, si quizás solo desea recuperar el mes y el año en que se crearon todos los usuarios, puede usar una declaración SQL como:

 SELECT id, MONTH(created_at), YEAR(created_at) FROM users;

Esto recuperaría todos los registros dentro de la tabla de usuarios y mostraría el número de identificación, el mes y el año en el que se creó cada usuario.

Agrupar registros por período de fecha

Un uso excelente de las funciones de fecha es la capacidad de agrupar registros por período de fecha usando GROUP BY dentro de sus declaraciones SQL. Por ejemplo, tal vez desee extraer la cantidad total de todos los pedidos en 2020 agrupados por mes. Puede usar una declaración SQL como:

 SELECT MONTH(created_at), SUM(amount) FROM orders WHERE created_at BETWEEN '2020-01-01 00:00:00' AND '2020-12-31 23:59:59' GROUP BY MONTH(created_at);

Esto recuperaría todos los pedidos realizados en el año 2020, los agruparía por el mes en que se crearon y devolvería 12 registros que muestran la cantidad total solicitada cada mes del año.

Tenga en cuenta que para un mejor rendimiento del índice, siempre es mejor evitar el uso de funciones de fecha como YEAR () dentro de la cláusula WHERE de las declaraciones SQL y, en su lugar, use el operador BETWEEN como se muestra en el ejemplo anterior.

Nunca más se confunda con las fechas

Con el conocimiento anterior, ahora puede trabajar, traducir y realizar operaciones de manera eficiente en fechas y horas en una amplia gama de casos de uso.

Recuerde siempre usar UTC cuando trabaje con fechas para simplificar, y utilice los consejos anteriores para administrar de manera eficiente las fechas dentro de su software, ya sea para completar cálculos simples o generar informes agrupados por períodos de fecha fácilmente.

Si es algo nuevo en SQL, asegúrese de consultar estos comandos SQL esenciales para ayudarlo a mejorar su uso de SQL.