Como Trabalhar com Datas e Horários no SQL

Introdução

Ao trabalhar com bancos de dados relacionais e Structured Query Language (SQL), pode haver momentos em que você precise trabalhar com valores que representam datas ou horários específicos. Por exemplo, você pode precisar calcular o total de horas gastas em uma determinada atividade, ou talvez precise manipular valores de data ou hora usando operadores matemáticos e funções de agregação para calcular sua soma ou média.

Neste tutorial, você aprenderá como usar datas e horas no SQL. Você começará realizando operações aritméticas e usando várias funções com datas e horas usando apenas a instrução SELECT. Em seguida, praticará executando consultas em dados de amostra e aprenderá como implementar a função CAST para tornar a saída mais fácil de ler.

Pré-requisitos

Para concluir este tutorial, você precisará:

• A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
• O MySQL instalado e seguro no servidor. Siga nosso guia Como Instalar o MySQL no Ubuntu 20.04 para configurar isso. Este guia assume que você também configurou um usuário MySQL não root, conforme descrito no Passo 3 deste guia.

Para praticar o uso de datas e horários neste tutorial, você precisará de um banco de dados e tabela carregados com dados de amostra. Se você não tiver um pronto para inserir, pode ler a seguinte Conectando-se ao MySQL e Configurando um Banco de Dados de Amostra seção para aprender como criar um banco de dados e uma tabela. Este tutorial se referirá a este banco de dados e tabela de amostra ao longo do texto.

Conectando-se ao MySQL e Configurando um Banco de Dados de Amostra

Se o seu banco de dados SQL estiver em um servidor remoto, faça login via SSH no seu servidor a partir da sua máquina local:

ssh sammy@your_server_ip

Em seguida, abra o prompt do MySQL, substituindo sammy pelas informações da sua conta de usuário MySQL:

mysql -u sammy -p

Crie um banco de dados chamado datetimeDB:

CREATE DATABASE datetimeDB;

Se o banco de dados for criado com sucesso, você receberá a seguinte saída:

Output
Query OK, 1 row affected (0.01 sec)

Para selecionar o banco de dados datetimeDB, execute a seguinte instrução USE:

USE datetimeDB;

Output
Database changed

Após selecionar o banco de dados, crie uma tabela dentro dele. Para o exemplo deste tutorial, vamos criar uma tabela que contenha os resultados de dois corredores em várias corridas que fizeram ao longo de um ano. Esta tabela terá as seguintes sete colunas:

• race_id: exibe valores do tipo de dados int e serve como a chave primária da tabela, significando que cada valor nesta coluna funcionará como um identificador único para sua respectiva linha.
• runner_name: utiliza o tipo de dados varchar com um máximo de 30 caracteres para os nomes dos dois corredores, Bolt e Felix.
• race_name: armazena os tipos de corridas com o tipo de dados varchar com um máximo de 20 caracteres.
• start_day: utiliza o tipo de dados DATE para rastrear a data de uma corrida específica por ano, mês e dia. Este tipo de dados segue os seguintes parâmetros: quatro dígitos para o ano e um máximo de dois dígitos para o mês e dia (AAAA-MM-DD).
• start_time: representa o horário de início da corrida com o tipo de dados TIME em horas, minutos e segundos (HH:MM:SS). Este tipo de dados segue um formato de relógio de 24 horas, como 15:00 para o equivalente às 15:00.
• total_miles: mostra a quilometragem total para cada corrida usando o tipo de dados decimal, já que muitas das milhas totais por corrida não são números inteiros. Neste caso, decimal especifica uma precisão de três com uma escala de um, significando que quaisquer valores nesta coluna podem ter três dígitos, com um desses dígitos estando à direita do ponto decimal.
• end_time: utiliza o tipo de dados TIMESTAMP para rastrear os tempos dos corredores no final da corrida. Este tipo de dados combina tanto a data quanto a hora em uma única string, e seu formato é uma combinação daqueles de DATE e TIME: (AAAA-MM-DD HH:MM:SS).

Crie a tabela executando o comando CREATE TABLE:

CREATE TABLE race_results (
race_id int, 
runner_name varchar(30),
race_name varchar(20), 
start_day DATE,
start_time TIME, 
total_miles decimal(3, 1),
end_time TIMESTAMP,
PRIMARY KEY (race_id)
); 

Em seguida, insira alguns dados de exemplo na tabela vazia:

INSERT INTO race_results
(race_id, runner_name, race_name, start_day, start_time, total_miles, end_time)
VALUES
(1, 'bolt', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:06:30'),
(2, 'bolt', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:22:31'),
(3, 'bolt', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 10:38:05'),
(4, 'bolt', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 07:39:04'),
(5, 'bolt', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 11:23:10'),
(6, 'felix', '1600_meters', '2022-09-18', '7:00:00', 1.0, '2022-09-18 7:07:15'),
(7, 'felix', '5K', '2022-10-19', '11:00:00', 3.1, '2022-10-19 11:30:50'),
(8, 'felix', '10K', '2022-11-20', '10:00:00', 6.2, '2022-11-20 11:10:17'),
(9, 'felix', 'half_marathon', '2022-12-21', '6:00:00', 13.1, '2022-12-21 08:11:57'),
(10, 'felix', 'full_marathon', '2023-01-22', '8:00:00', 26.2, '2023-01-22 12:02:10');

Output
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

Depois de inserir os dados, você está pronto para começar a praticar algumas operações aritméticas e funções com datas e horas no SQL.

Usando Aritmética com Datas e Horas

No SQL, você pode manipular valores de datas e horas usando expressões matemáticas. Tudo o que é necessário é o operador matemático e os valores que você deseja calcular.

Como exemplo, digamos que você queira encontrar uma data que seja um certo número de dias após outra. A seguinte consulta leva um valor de data (2022-10-05) e adiciona 17 a ele para retornar o valor da data dezessete dias após a especificada na consulta. Observe que este exemplo especifica 2022-10-05 como um valor de DATE para garantir que o SGBD não o interprete como uma string ou outro tipo de dado:

SELECT DATE '2022-10-05' + 17 AS new_date;

Output
+----------+
| new_date |
+----------+
| 20221022 |
+----------+
1 row in set (0.01 sec)

Como este resultado indica, 17 dias após 2022-10-05 é 2022-10-22, ou 22 de outubro de 2022.

Como outro exemplo, digamos que você queira calcular o total de horas entre dois horários diferentes. Você pode fazer isso subtraindo os dois horários um do outro. Para a seguinte consulta, 11:00 é o primeiro valor de horário e 3:00 é o segundo valor de horário. Aqui você precisará especificar que ambos são valores de TIME para retornar a diferença em horas:

SELECT TIME '11:00' - TIME '3:00' AS time_diff;

Output
+-----------+
| time_diff |
+-----------+
|     80000 |
+-----------+
1 row in set (0.00 sec)

Este resultado indica que a diferença entre 11:00 e 3:00 é 80000, ou 8 horas.

Agora pratique usando aritmética nas informações de data e hora dos dados de exemplo. Para a primeira consulta, calcule o tempo total que os corredores levaram para terminar cada corrida subtraindo end_time de start_time:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Você notará que este resultado na coluna total_time é bastante longo e difícil de ler. Mais tarde, demonstraremos como usar a função CAST para converter esses valores de dados para que fiquem mais claros de ler.

Agora, se você estivesse interessado apenas no desempenho de cada corredor em corridas mais longas, como meias e maratonas completas, você pode consultar seus dados para recuperar essas informações. Para essa consulta, subtraia end_time de start_time e restrinja seus resultados usando a cláusula WHERE para recuperar dados onde total_miles eram maiores que 12:

SELECT runner_name, race_name, end_time - start_time AS half_full_results
FROM race_results 
WHERE total_miles > 12;

Output
+-------------+---------------+-------------------+
| runner_name | race_name     | half_full_results |
+-------------+---------------+-------------------+
| bolt        | half_marathon |    20221221013904 |
| bolt        | full_marathon |    20230122032310 |
| felix       | half_marathon |    20221221021157 |
| felix       | full_marathon |    20230122040210 |
+-------------+---------------+-------------------+
4 rows in set (0.00 sec)

Nesta seção, você realizou algumas operações aritméticas em datas e horários com a instrução SELECT e para fins práticos nos dados de exemplo. Em seguida, você praticará consultas usando várias funções de data e hora.

Usando Funções de Data e Hora e Expressões de Intervalo

Há várias funções que podem ser usadas para encontrar e manipular valores de data e hora no SQL. As funções SQL são tipicamente usadas para processar ou manipular dados, e as funções disponíveis dependem da implementação do SQL. No entanto, a maioria das implementações do SQL permite que você encontre a data e hora atual consultando os valores current_date e current_time.

Para encontrar a data de hoje, por exemplo, a sintaxe é curta e composta apenas pela instrução SELECT e pela função current_date como no seguinte exemplo:

SELECT current_date;

Output
+--------------+
| current_date |
+--------------+
| 2022-02-15   |
+--------------+
1 row in set (0.00 sec)

Usando a mesma sintaxe, você pode encontrar a hora atual com a função current_time:

SELECT current_time;

Output
+--------------+
| current_time |
+--------------+
| 17:10:20     |
+--------------+
1 row in set (0.00 sec)

Se preferir consultar tanto a data quanto a hora na saída, utilize a função current_timestamp:

SELECT current_timestamp;

Output
+---------------------+
| current_timestamp   |
+---------------------+
| 2022-02-15 19:09:58 |
+---------------------+
1 row in set (0.00 sec)

Você pode usar funções de data e hora como estas dentro de funções aritméticas semelhantes à seção anterior. Por exemplo, digamos que você queira saber qual era a data há 11 dias a partir da data de hoje. Nesse caso, você poderia usar a mesma estrutura de sintaxe que usou anteriormente para consultar a função current_date e então subtrair 11 dela para encontrar a data de onze dias atrás:

SELECT current_date - 11;

Output
+-------------------+
| current_date - 11 |
+-------------------+
|          20220206 |
+-------------------+
1 row in set (0.01 sec)

Como esta saída indica, há 11 dias a partir da current_date (no momento desta escrita) foi 2022-02-06, ou 6 de fevereiro de 2022. Agora, tente executar esta mesma operação, mas substitua current_date pela função current_time:

SELECT current_time - 11;

Output
+-------------------+
| current_time - 11 |
+-------------------+
|        	233639 |
+-------------------+
1 row in set (0.00 sec)

Esta saída mostra que quando você subtrai 11 do valor de current_time, ele subtrai 11 segundos. A operação que você executou anteriormente usando a função current_date interpretou 11 como dias, não segundos. Essa inconsistência na interpretação de números ao trabalhar com funções de data e hora pode ser confusa. Em vez de exigir que você manipule valores de data e hora usando aritmética como esta, muitos sistemas de gerenciamento de banco de dados permitem que você seja mais explícito por meio do uso de expressões INTERVAL.

As expressões INTERVAL permitem que você encontre qual será a data ou hora antes ou depois de um intervalo definido a partir de uma expressão de data ou hora fornecida. Elas devem seguir a seguinte forma:

INTERVAL value unit

Por exemplo, para encontrar a data daqui a cinco dias, você poderia executar a seguinte consulta:

SELECT current_date + INTERVAL '5' DAY AS "5_days_from_today";

Este exemplo encontra o valor de current_date e então adiciona a expressão de intervalo INTERVAL '5' DAY a ele. Isso retorna a data de 5 dias a partir de agora:

Output
+-------------------+
| 5_days_from_today |
+-------------------+
| 2022-03-06    	|
+-------------------+
1 row in set (0.00 sec)

Isto é muito menos ambíguo do que a consulta seguinte, que produz uma saída similar, embora não idêntica:

SELECT current_date + 5 AS "5_days_from_today";

Output
+-------------------+
| 5_days_from_today |
+-------------------+
|      	20220306 |
+-------------------+
1 row in set (0.00 sec)

Observe que você também pode subtrair intervalos de datas ou horários para encontrar valores de antes da data especificada:

SELECT current_date - INTERVAL '7' MONTH AS "7_months_ago";

Output
+--------------+
| 7_months_ago |
+--------------+
| 2021-08-01   |
+--------------+
1 row in set (0.00 sec)

As unidades disponíveis para uso em expressões INTERVAL dependem da sua escolha de SGBD, embora a maioria tenha opções como HOUR, MINUTE e SECOND:

SELECT current_time + INTERVAL '6' HOUR AS "6_hours_from_now",
current_time - INTERVAL '5' MINUTE AS "5_minutes_ago",
current_time + INTERVAL '20' SECOND AS "20_seconds_from_now";

Output
+------------------+---------------+---------------------+
| 6_hours_from_now | 5_minutes_ago | 20_seconds_from_now |
+------------------+---------------+---------------------+
| 07:51:43     	| 01:46:43  	| 01:52:03.000000 	|
+------------------+---------------+---------------------+
1 row in set (0.00 sec)

Agora que você aprendeu sobre expressões de intervalo e algumas funções de data e hora, continue praticando com os dados de amostra que você inseriu no primeiro passo.

Usando CAST e Funções de Agregação com Data e Hora

Lembre-se do terceiro exemplo na seção Usando Aritmética com Datas e Horários, quando você executou a seguinte consulta para subtrair end_time de start_time para calcular as horas totais que cada corredor completou por corrida. No entanto, a saída resultou em uma coluna contendo uma saída muito longa, que segue o tipo de dados TIMESTAMP que foi configurado na tabela:

SELECT runner_name, race_name, end_time - start_time 
AS total_time 
FROM race_results;

Output
+-------------+---------------+----------------+
| runner_name | race_name     | total_time     |
+-------------+---------------+----------------+
| bolt        | 1600_meters   | 20220918000630 |
| bolt        | 5K            | 20221019002231 |
| bolt        | 10K           | 20221120003805 |
| bolt        | half_marathon | 20221221013904 |
| bolt        | full_marathon | 20230122032310 |
| felix       | 1600_meters   | 20220918000715 |
| felix       | 5K            | 20221019003050 |
| felix       | 10K           | 20221120011017 |
| felix       | half_marathon | 20221221021157 |
| felix       | full_marathon | 20230122040210 |
+-------------+---------------+----------------+
10 rows in set (0.00 sec)

Porque você está realizando uma operação com duas colunas que têm tipos de dados diferentes (end_time armazenando valores TIMESTAMP e start_time armazenando valores TIME), o banco de dados não sabe qual tipo de dado usar ao imprimir o resultado da operação. Em vez disso, ele converte ambos os valores em inteiros para poder realizar a operação, resultando nos longos números na coluna total_time.

Para ajudar a tornar esses dados mais claros para ler e interpretar, você pode usar a função CAST para converter esses longos valores inteiros para o tipo de dado TIME. Para fazer isso, comece com CAST e siga imediatamente com um parêntese de abertura, os valores que você deseja converter e depois a palavra-chave AS e o tipo de dado para o qual você deseja convertê-lo.

A consulta a seguir é idêntica ao exemplo anterior, mas usa a função CAST para converter a coluna total_time para o tipo de dado time:

SELECT runner_name, race_name, CAST(end_time - start_time AS time)
AS total_time 
FROM race_results;

Output
+-------------+---------------+------------+
| runner_name | race_name     | total_time |
+-------------+---------------+------------+
| bolt        | 1600_meters   | 00:06:30   |
| bolt        | 5K            | 00:22:31   |
| bolt        | 10K           | 00:38:05   |
| bolt        | half_marathon | 01:39:04   |
| bolt        | full_marathon | 03:23:10   |
| felix       | 1600_meters   | 00:07:15   |
| felix       | 5K            | 00:30:50   |
| felix       | 10K           | 01:10:17   |
| felix       | half_marathon | 02:11:57   |
| felix       | full_marathon | 04:02:10   |
+-------------+---------------+------------+
10 rows in set (0.00 sec)

CAST converteu os valores de dados para TIME nesta saída, tornando-a muito mais fácil de ler e entender.

Agora, vamos usar algumas funções agregadas em combinação com a função CAST para encontrar os tempos mais curtos, mais longos e totais de cada corredor. Primeiro, consulte o tempo mínimo (ou mais curto) usando a função agregada MIN. Novamente, você vai querer usar CAST para converter os valores de dados TIMESTAMP em valores de dados TIME para clareza. Note que ao usar duas funções como neste exemplo, são necessários dois pares de parênteses e o cálculo para as horas totais (end_time - start_time) deve ser aninhado dentro de um deles. Por último, adicione uma cláusula GROUP BY para organizar esses valores com base na coluna runner_name para que a saída apresente os resultados da corrida dos dois corredores:

SELECT runner_name, MIN(CAST(end_time - start_time AS time)) AS min_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | min_time |
+-------------+----------+
| bolt        | 00:06:30 |
| felix       | 00:07:15 |
+-------------+----------+
2 rows in set (0.00 sec)

Esta saída mostra o tempo mais curto de corrida de cada corredor, neste caso, um mínimo de seis minutos e 30 segundos para Bolt, e sete minutos e 15 segundos para Felix.

Em seguida, encontre o tempo mais longo de corrida de cada corredor. Você pode usar a mesma sintaxe da consulta anterior, mas desta vez substitua MIN por MAX:

SELECT runner_name, MAX(CAST(end_time - start_time AS time)) AS max_time
FROM race_results GROUP BY runner_name;

Output
+-------------+----------+
| runner_name | max_time |
+-------------+----------+
| bolt        | 03:23:10 |
| felix       | 04:02:10 |
+-------------+----------+
2 rows in set (0.00 sec)

Esta saída nos diz que o tempo mais longo de corrida de Bolt foi um total de três horas, 23 minutos e 10 segundos; e de Felix foi um total de quatro horas, dois minutos e 10 segundos.

Agora vamos consultar algumas informações de alto nível sobre o total de horas que cada corredor passou correndo. Para esta consulta, combine a função de agregação SUM para encontrar a soma total de horas com base em end_time - start_time, e use CAST para converter esses valores de dados em TIME. Não se esqueça de incluir GROUP BY para organizar os valores para os resultados de ambos os corredores:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
+-------------+-------------+
| runner_name | total_hours |
+-------------+-------------+
| bolt        |       52880 |
| felix       |       76149 |
+-------------+-------------+
2 rows in set (0.00 sec)

Curiosamente, esta saída mostra a interpretação para o MySQL, que na verdade está calculando o tempo total como inteiros. Se ler-mos esses resultados como tempo, o tempo total de Bolt se desdobra em cinco horas, 28 minutos e 80 segundos; e o tempo de Felix se desdobra em sete horas, 61 minutos e 49 segundos. Como pode ver, essa divisão de tempo não faz sentido, o que indica que está sendo calculado como um número inteiro e não como tempo. Se tentasse isso em um DBMS diferente, como o PostgreSQL, por exemplo, a mesma consulta teria uma aparência ligeiramente diferente:

SELECT runner_name, SUM(CAST(end_time - start_time AS time))
AS total_hours FROM race_results GROUP BY runner_name;

Output
 runner_name | total_hours
-------------+-------------
 felix       | 10:01:44
 bolt        | 06:09:20
(2 rows)

Neste caso, a consulta no PostgreSQL interpreta os valores como tempo e os calcula como tal, de modo que os resultados de Felix se desdobram em um total de 10 horas, um minuto e 44 segundos; e os de Bolt como seis horas, nove minutos e 20 segundos. Este é um exemplo de como várias implementações de DBMS podem interpretar valores de dados de forma diferente, mesmo que esteja usando a mesma consulta e conjunto de dados.

Conclusão

Entender como usar data e hora no SQL é útil ao consultar resultados específicos, como minutos, segundos, horas, dias, meses, anos; ou uma combinação de todos esses. Além disso, existem muitas funções disponíveis para datas e horas que facilitam a busca de valores específicos, como a data ou hora atual. Embora este tutorial tenha usado apenas operações de adição e subtração em datas e horas no SQL, você pode usar valores de data e hora com qualquer expressão matemática. Saiba mais em nosso guia sobre expressões matemáticas e funções de agregação e experimente-as em suas consultas de data e hora.