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

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

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 eles de forma eficiente 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.





Trabalho 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.



o que significa tbh no facebook

Quando necessário, você pode facilmente converter o fuso horário de qualquer valor de data / hora / carimbo de data / hora com o prático CONVERT_TZ () Função 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');

Isto resulta em 2021-02-04 13:47:23 o que é exatamente correto. Os três argumentos passaram para CONVERT_TZ () são primeiro a data / hora / carimbo de data / hora com que você está começando (use agora () para a hora atual), o segundo sempre será '+0: 00' já que 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 o excelente DATE_ADD () e DATE_SUB () funções que tornam 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 que ocorreram 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 do logins tabela com uma data de login maior que a hora atual menos 45 minutos, ou seja, os últimos 45 minutos.

Obtenha a diferença entre as datas

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

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

o DATEDIFF A função recebe dois argumentos, ambos 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, o TO_SECONDS () função 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 de tais 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 já que 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 A.D. 0.
- TO_SECONDS() - The number of seconds since A.D. 0.
- UNIX_TIMESTAMP() - The number of seconds since the epoch (Jan 1st, 1970)

Por exemplo, se você quiser apenas 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 dentro do Comercial tabela e mostra o 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 GRUPO POR dentro de 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 ANO() dentro da cláusula WHERE das instruções SQL e, em vez disso, use o ENTRE operador como mostrado no exemplo acima.

Nunca mais se confunda com datas

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

Lembre-se de sempre usar 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 relatórios agrupados por períodos de datas com facilidade.

Se você é novo em SQL, certifique-se de verificar estes comandos SQL essenciais para ajudar a melhorar seu uso de SQL.

Compartilhado Compartilhado Tweet O email Como criar relatórios e documentos profissionais no Microsoft Word

Este guia examina os elementos de um relatório profissional e analisa a estruturação, o estilo e a finalização do seu documento no Microsoft Word.

Leia a seguir Tópicos relacionados Sobre o autor Matt Dizak(18 artigos publicados) Mais de Matt Dizak

Assine a nossa newsletter

Junte-se ao nosso boletim informativo para dicas de tecnologia, análises, e-books grátis e ofertas exclusivas!

Clique aqui para se inscrever