Como trabalhar com eficácia com datas e horários no MySQL

fevereiro 11, 2021 gurinho

Datas e horários são importantes, ajudam a manter as coisas organizadas e são um aspecto integrante de qualquer operação de software.

Trabalhar com eficiência com eles no banco de dados às vezes pode parecer confuso, seja trabalhar em vários fusos horários, adicionar / subtrair datas e outras operações.

Aprenda as várias funções do MySQL disponíveis para manipular e gerenciar facilmente datas / horas em seu banco de dados.

Trabalhando com fusos horários

Para ajudar a manter as coisas padronizadas, você só deve trabalhar com datas / horas no fuso horário UTC. Cada vez que você estabelece uma conexão com o banco de dados MySQL, deve mudar o fuso horário para UTC, o que pode ser feito com a seguinte instrução SQL:

 SET TIME_ZONE = '+0:00'

Como todas as datas agora serão salvas em UTC, você sempre sabe com o que está trabalhando, tornando as coisas mais simples e diretas.

Quando necessário, você pode converter facilmente o fuso horário de qualquer valor de data / hora / carimbo de data / hora com a útil função CONVERT_TZ () do MySQL. Você precisa saber o deslocamento primeiro, por exemplo, PST na costa oeste da América do Norte é UTC -08: 00, então você pode usar:

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

Isso resulta em 2021-02-04 13:47:23 que é exatamente correto. Os três argumentos passados para CONVERT_TZ () são primeiro a data / hora / hora com que você está começando (use agora () para a hora atual), o segundo sempre será '+0: 00', pois todas as datas são forçadas para UTC no banco de dados , e o último é o deslocamento para o qual desejamos converter a data.

Adicionar / subtrair datas

Muitas vezes você precisa adicionar ou subtrair de datas, como se você precisasse recuperar registros de uma semana atrás ou agendar algo daqui a um mês.

Felizmente, o MySQL tem as excelentes funções DATE_ADD () e DATE_SUB () tornando esta tarefa extremamente fácil. Por exemplo, você pode subtrair duas semanas da data atual com a instrução SQL:

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

Se, em vez disso, você quisesse adicionar três dias a um carimbo de data / hora existente, usaria:

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

Ambas as funções funcionam da mesma forma, o primeiro argumento é o carimbo de data / hora com o qual você está começando e o segundo argumento é o intervalo a ser adicionado ou subtraído. O segundo argumento é sempre formatado da mesma forma, começando com a palavra intervalo seguido por um valor numérico e o próprio intervalo, que pode ser qualquer um dos seguintes: segundo, minuto, hora, dia, semana, mês, trimestre, ano.

Em outro exemplo, se você quiser recuperar todos os logins ocorridos nos últimos 34 minutos, poderá usar uma instrução SQL, como:

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

Como você pode ver, isso recuperaria todos os registros da tabela de logins com uma data de login maior que a hora atual menos 45 minutos, ou em outras palavras, os últimos 45 minutos.

Obtenha a diferença entre as datas

Às vezes, você precisa saber quanto tempo passou entre duas datas. Você pode obter facilmente o número de dias entre duas datas diferentes com a função DATEDIFF , como a instrução SQL abaixo:

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

A função DATEDIFF leva dois argumentos, ambos os quais são carimbos de data / hora e fornece o número de dias entre eles. O exemplo acima mostrará o número de dias decorridos de 15 de dezembro de 2020 até hoje.

Para obter o número de segundos entre duas datas, a função TO_SECONDS () pode ser útil, por exemplo:

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

Isso resultará no número de segundos entre as duas datas fornecidas.

Extrair segmentos de datas

Existem várias funções do MySQL que permitem extrair facilmente segmentos específicos de datas, como se você quisesse apenas o mês, o dia do ano ou a hora. Aqui estão alguns exemplos dessas funções:

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

 SELECT HOUR(now());

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

As instruções SQL acima resultariam em 02 , a hora atual, e 196, pois 15 de setembro é o 196º dia do ano. Aqui está uma lista de todas as funções de extração de data disponíveis, cada uma recebendo apenas um argumento, a data sendo extraída 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 exemplo, se talvez você só queira recuperar o mês e o ano em que todos os usuários foram criados, você pode usar uma instrução SQL como:

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

Isso recuperaria todos os registros da tabela de usuários e mostraria o número do id, mês e ano em que cada usuário foi criado.

Agrupando registros por período de datas

Um excelente uso das funções de data é a capacidade de agrupar registros por período de data usando GROUP BY em suas instruções SQL. Por exemplo, talvez você queira extrair o valor total de todos os pedidos em 2020 agrupados por mês. Você pode usar uma instrução 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);

Isso recuperaria todos os pedidos feitos no ano 2020, agruparia pelo mês em que foram criados e retornaria 12 registros mostrando a quantidade total solicitada em cada mês do ano.

Observe que para um melhor desempenho do índice, é sempre melhor evitar o uso de funções de data, como YEAR () na cláusula WHERE das instruções SQL e, em vez disso, use o operador BETWEEN conforme mostrado no exemplo acima.

Nunca mais se confunda com datas

Usando o conhecimento acima, agora você pode trabalhar, traduzir e executar operações em datas e horas de maneira eficiente em uma ampla variedade de casos de uso.

Lembre-se de sempre usar o UTC ao trabalhar com datas para simplificar e utilizar as dicas acima para gerenciar datas com eficiência em seu software, seja para concluir cálculos simples ou obter facilmente relatórios agrupados por períodos de datas.

Se você for um pouco novo em SQL, certifique-se de verificar esses comandos SQL essenciais para ajudar a melhorar seu uso de SQL.