Como trabalhar com datas e horários em SQL

Introdução

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

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

Pré-requisitos

Para completar 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 pressupõe que você também configurou um usuário MySQL não root, conforme descrito no Passo 3 deste guia.

Para praticar o uso de data e hora neste tutorial, você precisará de um banco de dados e uma tabela carregados com dados de exemplo. Se você não tiver um pronto para inserir, você pode ler a seguinte Conectando-se ao MySQL e Configurando um Banco de Dados de Exemplo 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 exemplo ao longo dele.

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

Se o seu banco de dados SQL estiver em um servidor remoto, faça 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 do 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

Depois de selecionar o banco de dados, crie uma tabela dentro dele. Para o exemplo deste tutorial, vamos criar uma tabela que armazena os resultados de dois corredores em várias corridas que eles participaram ao longo de um ano. Esta tabela terá sete colunas:

• race_id: exibe valores do tipo de dados int e serve como a chave primária da tabela, o que significa 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 no máximo 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 por 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 dado 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, o que significa que quaisquer valores nesta coluna podem ter três dígitos, com um desses dígitos à direita do ponto decimal.
• end_time: usa o tipo de dado TIMESTAMP para rastrear os horários 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 dos formatos 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ê estará pronto para começar a praticar algumas operações aritméticas e funções com data e hora no SQL.

Usando Aritmética com Datas e Horas

No SQL, você pode manipular valores de data e hora 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 consulta a seguir recebe um valor de data (2022-10-05) e adiciona 17 a ele para retornar o valor da data dezessete dias após o especificado na consulta. Observe que este exemplo especifica 2022-10-05 como um valor de DATE para garantir que o SGDB 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 esta saída 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 tempo e 3:00 é o segundo valor de tempo. 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)

Esta saída 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 amostra. 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 esta saída na coluna total_time é bastante longa e difícil de ler. Mais tarde, demonstraremos como usar a função CAST para converter esses valores de dados para que sejam mais claros de ler.

Agora, se você estiver interessado apenas no desempenho de cada corredor em corridas mais longas, como meias maratonas e maratonas completas, você pode consultar seus dados para recuperar essas informações. Para esta 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 alguns cálculos aritméticos 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

Existem 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 geralmente usadas para processar ou manipular dados, e as funções disponíveis dependem da implementação do SQL. A maioria das implementações de SQL, no entanto, permite que você encontre a data e hora atuais consultando os valores de 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, use 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 essas dentro de funções aritméticas semelhantes à seção anterior. Por exemplo, digamos que você queira saber qual era a data 11 dias atrás 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 essa saída indica, onze dias atrás da current_date (no momento desta escrita) foi 2022-02-06, ou 6 de fevereiro de 2022. Agora tente executar a 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 como segundos. Essa inconsistência na forma como os números são interpretados 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 assim, 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 seria a data ou hora antes ou depois de um intervalo definido a partir de uma expressão de data ou hora dada. Elas devem seguir a seguinte forma:

INTERVAL value unit

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

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

Este exemplo encontra o valor 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)

Isso é muito menos ambíguo do que a seguinte consulta, que produz saída semelhante, 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 você usar em expressões de 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 trabalhando com os dados de exemplo 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. A saída, no entanto, 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 contendo valores TIMESTAMP e start_time contendo valores TIME), o banco de dados não sabe qual tipo de dados usar ao imprimir o resultado da operação. Em vez disso, converte ambos os valores em inteiros para que possa realizar a operação, resultando nos números longos 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 valores inteiros longos para o tipo de dados TIME. Para fazer isso, comece com CAST e siga imediatamente com um parêntese de abertura, os valores que você deseja converter e então a palavra-chave AS e o tipo de dados para o qual deseja convertê-lo.

A consulta a seguir é idêntica ao exemplo anterior, mas usa uma função CAST para converter a coluna total_time para o tipo de dados 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 resultados de tempo mais curtos, mais longos e totais de cada corredor. Primeiro, consulte o valor mínimo (ou mais curto) de tempo gasto com 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. Observe 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 fim, 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 o 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 agregada 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 dos 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 MySQL, que na verdade está calculando o tempo total como inteiros. Se lermos 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 você pode perceber, essa decomposição do tempo não faz sentido, o que indica que está sendo calculada como um inteiro e não como tempo. Se você tentasse isso em um DBMS diferente, como 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 em 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 estejam 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 adição e subtração aritmética 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 com suas consultas de data e hora.