Pesquisa de site

Melhore suas habilidades de codificação com valores temporais no MySQL

Esta visão geral dos valores de data e hora no MySQL o ajudará a aproveitar as vantagens dos valores temporais nas tabelas do seu banco de dados.

Tanto usuários novos quanto experientes do popular sistema de banco de dados MySQL podem muitas vezes ficar confusos sobre como os valores temporais são tratados pelo banco de dados. Às vezes, os usuários não se preocupam em aprender muito sobre os tipos de dados de valor temporal. Isso pode ser porque eles acham que não há muito o que saber sobre eles. Um encontro é um encontro, certo? Bem, nem sempre. Dedicar alguns minutos para aprender como o MySQL armazena e exibe datas e horas é benéfico. Aprender como aproveitar melhor os valores temporais nas tabelas do seu banco de dados pode ajudar a torná-lo um codificador melhor.

Tipos de dados temporais MySQL

Ao construir suas tabelas no MySQL, você escolhe o tipo de dados adequado que contém de forma mais eficiente os dados que pretende inserir na tabela (INT, FLOAT, CHAR e assim por diante). MySQL fornece cinco tipos de dados para valores temporais. São eles: DATE, TIME, DATETIME, TIMESTAMP e YEAR.

MySQL usa o formato ISO 8601 para armazenar os valores nos seguintes formatos:

  • DATA  AAAA-MM-DD
  • HORA   HH:MM:SS
  • TIMESTAMP AAAA-MM-DD  HH:MM:SS
  • ANO AAAA

Data e hora comparada ao carimbo de data/hora

Você deve ter notado que os tipos de dados DATETIME e TIMESTAMP contêm os mesmos dados. Você pode estar se perguntando se há alguma diferença entre os dois. Existem diferenças.

Primeiro, o intervalo de datas que pode ser usado é diferente. DATETIME pode conter datas entre 1000-01-01 00:00:00 e 9999-12-31 23:59:59, enquanto TIMESTAMP tem um intervalo muito mais limitado de 01/01/1970 00:00:01 a 19/01/2038 03:14:07 UTC.

Em segundo lugar, embora ambos os tipos de dados permitam auto_initialize ou auto_update seus respectivos valores (com DEFAULT CURRENT_TIMESTAMP e ON UPDATE CURRENT_TIMESTAMP respectivamente), isso não estava disponível para valores DATETIME até a versão 5.6.5. Você pode usar um dos sinônimos do MySQL para CURRENT_TIMESTAMP se desejar, como NOW() ou LOCALTIME().

[ Baixe agora: folha de referências do MariaDB e MySQL ]

Se você usar ON UPDATE CURENT_TIMESTAMP (ou um de seus sinônimos) para um valor DATETIME, mas não usar a cláusula DEFAULT CURRENT_TIMESTAMP, então o a coluna será padronizada como NULL. Isso acontece a menos que você inclua NOT NULL na definição da tabela, caso em que o padrão é zero.

Outra coisa importante a ter em mente é que embora normalmente nem uma coluna DATETIME nem uma coluna TIMESTAMP tenham um valor padrão, a menos que você declare um, há uma exceção a esta regra. A primeira coluna TIMESTAMP da sua tabela é criada implicitamente com as cláusulas DEFAULT CURRENT_TIMESTAMP e ON UPDATE CURRENT_TIMESTAMP se nenhuma delas for especificada e se a variável explicit_defaults_for_timestamp está desabilitado.

Para verificar o status desta variável, execute:

mysql> show variables like 'explicit_default%';

Se você quiser ligar ou desligar, execute este código, usando 0 para desligar e 1 para ligar:

mysql> set explicit_defaults_for_timestamp = 0;

Tempo

O tipo de dados TIME do MySQL pode parecer bastante simples, mas há algumas coisas que um bom programador deve ter em mente.

Em primeiro lugar, esteja ciente de que, embora o tempo seja muitas vezes considerado como a hora do dia, na verdade é um tempo decorrido. Ou seja, pode ser um valor negativo ou maior que 23:59:59. Um valor TIME no MySQL pode estar no intervalo de -838:59:59 a 838:59:59.

Além disso, se você abreviar um valor de tempo, o MySQL o interpretará de maneira diferente dependendo se você usar dois pontos. Por exemplo, o valor 10:34 é visto pelo MySQL como 10:34:00. Ou seja, 34 minutos depois das dez horas. Mas se você deixar de fora os dois pontos, 1034', o MySQL verá isso como 00:10:34. Ou seja, dez minutos e 34 segundos.

Finalmente, você deve saber que os valores TIME (bem como a parte de tempo das colunas DATETIME e TIMESTAMP) podem, a partir da versão 5.6.4 , pegue uma unidade fracionária. Para usá-lo, adicione um número inteiro (valor máximo seis) entre parênteses no final da definição do tipo de dados.

time_column TIME(2)

Fusos horários

As mudanças de fuso horário não só causam confusão e fadiga no mundo real, mas também são conhecidas por causarem problemas em sistemas de banco de dados. A Terra está dividida em 24 fusos horários distintos que geralmente mudam a cada 15 graus de longitude. Digo normalmente porque algumas nações optam por fazer as coisas de forma diferente. A China, por exemplo, opera num único fuso horário, em vez dos cinco que seriam esperados.

A questão é: como você lida com usuários de um sistema de banco de dados que estão em fusos horários diferentes. Felizmente, o MySQL não torna isso muito difícil.

Para verificar o fuso horário da sua sessão, execute:

mysql> select @@session.time_zone;

Se aparecer System, significa que ele está usando o fuso horário definido em seu arquivo de configuração my.cnf . Se você estiver executando o servidor MsSQL em seu computador local, provavelmente será isso que você obterá e não precisará fazer nenhuma alteração.

Se você quiser alterar o fuso horário da sua sessão, execute um comando como:

mysql> set time_zone = '-05:00';

Isso define seu fuso horário cinco horas atrás do UTC. (EUA/Leste).

Obtendo o dia da semana

Para acompanhar o código no restante deste tutorial, você deve criar uma tabela com valores de data em seu sistema. Por exemplo:

mysql> create table test
( row_id smallint not null auto_increment primary key,
the_date date not null);

Em seguida, insira algumas datas aleatórias na tabela usando o formato ISO 8601, como:

mysql> insert into test (the_date) VALUES ('2022-01-05');

Coloquei quatro linhas de valores de data em minha tabela de teste, mas coloquei quantos você desejar.

Às vezes, você pode querer saber em que dia da semana ocorreu um determinado dia. O MySQL oferece algumas opções.

A primeira, e talvez a mais óbvia, é usar a função DAYNAME(). Usando a tabela de exemplo, DAYNAME() informa o dia da semana para cada uma das datas:

mysql> SELECT the_date, DAYNAME(the_date) FROM test ;
+------------+-------------------------------+
| the_date   | DAYNAME(the_date)             |
+------------+-------------------------------+
| 2021-11-02 | Tuesday                       |
| 2022-01-05 | Wednesday                     |
| 2022-05-03 | Tuesday                       |
| 2023-01-13 | Friday                        |
+------------+-------------------------------+
4 rows in set (0.00 sec)

Os outros dois métodos para obter o dia da semana retornam valores inteiros em vez do nome do dia. Eles são WEEKDAY() e DAYOFWEEK(). Ambos retornam números, mas não retornam o mesmo número. A função WEEKDAY() retorna um número de 0 a 6, sendo 0 segunda-feira e 6 domingo. Por outro lado, DAYOFWEEK() retorna um número de 1 a 7, sendo 1 domingo e 7 sábado.

mysql> SELECT the_date, DAYNAME(the_date),
WEEKDAY(the_date), DAYOFWEEK(the_date) FROM test;
+------------+------------------+------------------+--------------------+
| the_date   | DAYNAME(the_date)| WEEKDAY(the_date)| DAYOFWEEK(the_date)|
| 2021-11-02 | Tuesday          | 1                | 3                  |
| 2022-01-05 | Wednesday        | 2                | 4                  |
| 2022-05-03 | Tuesday          | 1                | 3                  |
| 2023-01-13 | Friday           | 4                | 6                  |
+------------+------------------+------------------+--------------------+
4 rows in set (0.00 sec)

Quando você só quer parte do encontro

Às vezes você pode ter uma data armazenada em sua tabela MySQL, mas deseja acessar apenas uma parte da data. Isso não é problema.

Existem várias funções com nomes convenientes no MySQL que permitem fácil acesso a uma parte específica de um objeto de data. Para mostrar apenas alguns exemplos:

mysql> SELECT the_date, YEAR(the_date), MONTHNAME(the_date), 
DAYOFMONTH(the_date) FROM test ;
+-----------+---------------+-------------------+---------------------+
| the_date  | YEAR(the_date)|MONTHNAME(the_date)| DAYOFMONTH(the_date)|
+-----------+---------------+-------------------+---------------------+
| 2021-11-02| 2021          | November          | 2                   |
| 2022-01-05| 2022          | January           | 5                   |
| 2022-05-03| 2022          | May               | 3                   |
| 2023-01-13| 2023          | January           | 13                  |
+-----------+---------------+-------------------+---------------------+
4 rows in set (0.00 sec)

O MySQL também permite que você use a função EXTRACT() para acessar uma parte de uma data. Os argumentos que você fornece para a função são um especificador de unidade (certifique-se de que seja singular), FROM e o nome da coluna. Então, para obter apenas o ano da nossa tabela de teste, você poderia escrever:

mysql> SELECT EXTRACT(YEAR FROM the_date) FROM test;
+----------------------------------------------+
| EXTRACT(YEAR FROM the_date)                  |
+----------------------------------------------+
| 2021                                         |
| 2022                                         |
| 2022                                         |
| 2023                                         |
+----------------------------------------------+
4 rows in set (0.01 sec)

Inserindo e lendo datas com diferentes formatos

Conforme mencionado anteriormente, o MySQL armazena valores de data e hora usando o formato ISO 8601. Mas e se você quiser armazenar valores de data e hora de outra forma, como MM-DD-AAAA para datas? Bem, primeiro, não tente. O MySQL armazena datas e horas no formato 8601 e é assim que as coisas são. Não tente mudar isso. No entanto, isso não significa que você precise converter seus dados para esse formato específico antes de inseri-los no banco de dados ou que não possa exibir os dados no formato que desejar.

Se você quiser inserir uma data em sua tabela formatada de maneira não ISO, você pode usar STR_TO_DATE(). O primeiro argumento é o valor da string da data que você deseja armazenar em seu banco de dados. O segundo argumento é a string de formatação que permite ao MySQL saber como a data está organizada. Vejamos um exemplo rápido e depois me aprofundarei um pouco mais no que se trata essa string de formatação de aparência estranha.

mysql> insert into test (the_date) values (str_to_date('January 13, 2023','%M %d, %Y'));

Query OK, 1 row affected (0.00 sec)

Você coloca a string de formatação entre aspas e precede cada um dos caracteres especiais com um sinal de porcentagem. A sequência de formato no código acima informa ao MySQL que minha data consiste em um nome de mês completo (%M), seguido por um dia de dois dígitos (%d), então uma vírgula e, finalmente, um ano de quatro dígitos (%Y). Observe que a capitalização é importante.

Alguns dos outros caracteres de string de formatação comumente usados são:

  • %b nome abreviado do mês (exemplo: janeiro)
  • %c mês numérico (exemplo: 1)
  • %W nome do dia (exemplo: sábado)
  • %a nome abreviado do dia (exemplo: Sábado)
  • %T 24 horas (exemplo: 22:01:22)
  • %r horário de 12 horas com AM/PM (exemplo: 22h01min22s)
  • %y ano com 2 dígitos (exemplo: 23)

Observe que para o ano de 2 dígitos (%y) o intervalo de anos é de 1970 a 2069. Portanto, os números de 70 a 99 são considerados do século 20, enquanto os números de 00 a 69 são considerados do século 21. século.

Se você tem uma data armazenada em seu banco de dados e deseja exibi-la usando um formato diferente, você pode usar a função DATE_FORMAT():

mysql> SELECT DATE_FORMAT(the_date, '%W, %b. %d, %y') FROM test;
+-----------------------------------------+
| DATE_FORMAT(the_date, '%W, %b. %d, %y') |
+-----------------------------------------+
| Tuesday, Nov. 02, 21                    |
| Wednesday, Jan. 05, 22                  |
| Tuesday, May. 03, 22                    |
| Friday, Jan. 13, 23                     |
+-----------------------------------------+
4 rows in set (0.00 sec)

Conclusão

Este tutorial deve fornecer uma visão geral útil dos valores de data e hora no MySQL. Espero que este artigo tenha lhe ensinado algo novo que lhe permita ter um melhor controle e uma maior compreensão de como seu banco de dados MySQL lida com valores temporais.