Funções de data e hora SQL em 5 dialetos SQL populares

Você está confuso com todas as funções de data e hora utilizadas em diferentes dialetos SQL? Neste artigo, eu resumo os tipos de dados de data e hora utilizados no PostgreSQL, Oracle, SQLite, MySQL, e T-SQL. Também forneço exemplos com as principais funções de data e hora SQL utilizadas através destes dialetos. É hora de se tornar gurus de data e hora!

Você quer calcular com que freqüência os funcionários estão atrasados para o trabalho? Ou quanto tempo leva para completar um pedido? Há muitos casos em que os analistas de dados precisam realizar cálculos sobre valores relacionados a data e hora em SQL. No entanto, os tipos de dados e funções de data e hora diferem significativamente entre os dialetos SQL. As consultas serão muito diferentes dependendo se você as escreve, digamos, no PostgreSQL ou no SQL Server.

Neste artigo, eu cubro o básico de lidar com datas e horas através de diferentes dialetos SQL. Também forneço exemplos de funções chave de data e hora.

Se você quiser pular para uma determinada parte, aqui estão os dialetos SQL cobertos neste artigo:

• PostgreSQL
• Oracle
• SQLite
• MySQL
• T-SQL (MS SQL Server)

Você também pode começar a aprender os tipos de dados de data e hora hoje com o curso de Tipos de Dados em SQL. Este curso interativo cobre tipos de dados que funcionam em todos os sistemas de gerenciamento de bancos de dados relacionais populares, incluindo SQL Server, MySQL, Oracle, e PostgreSQL.

Funções de Data e Hora SQL através de diferentes dialetos

É sempre mais fácil obter novas informações através de casos de uso no mundo real. Para entender como datas e horários podem ser processados através de diferentes dialetos SQL, sugiro utilizar a seguinte tabela que mostra as viagens planejadas para 2022. Aqui temos tanto as datas como os carimbos de data e hora com uma compensação de fuso horário.

Agora vamos ver como podemos lidar com estes dados em diferentes dialetos SQL.

Funções de Data e Hora PostgreSQL

Há quatro tipos de dados que processam datas e horas no PostgreSQL:

• O date O tipo de dados é usado para armazenar datas sem a hora exata (por exemplo, '2022-21-03').
• O time O tipo de dados nos permite armazenar tempo sem data (por exemplo, '8:34:59'). Por padrão, o tipo de dados de tempo não inclui informações sobre o fuso horário. Se for necessário especificar o fuso horário, será necessário use time with time zone. No entanto, recomenda-se usar o próximo tipo de dados ao lidar com os fusos horários.
• O timestamp O tipo de dados é muito útil na configuração prática, pois nos permite armazenar o carimbo de tempo completo - as datas com a hora exata (por exemplo, '2022-07-30 06:55:34'). A precisão pode ser de até 1 microssegundo. Como com o tipo de dados time, se você quiser incluir informações sobre o fuso horário, precisará usar timestamp com fuso horário.
• O interval O tipo de dados é usado para armazenar informações sobre intervalos de tempo (ou seja, duração). Você pode restringir o conjunto de campos armazenados adicionando uma frase correspondente (por exemplo YEAR, DAY, YEAR TO MONTH, HOUR TO SECOND); você pode obter mais detalhes na documentação do PostgreSQL. Estes intervalos podem ser adicionados ou subtraídos dos tipos de dados definidos acima.

Você pode ler mais sobre estes e outros tipos de dados do PostgreSQL neste artigo; vamos passar a um caso de uso prático.

Para criar nossa função de trips no PostgreSQL, utilizamos o seguinte código:

CREATE TABLE trips(
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp with time zone,
    arrival_timestamp timestamp with time zone
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 8:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Note que usamos o tipo de dados de data para armazenar informações sobre as datas de partida e retorno. Também usamos o carimbo de data com fuso horário para armazenar as horas de partida e chegada. Este tipo de dados nos permite armazenar informações sobre data, hora e fuso horário; como todas as nossas viagens atravessam vários fusos horários, isto é importante.

Para praticar as funções de data e hora do PostgreSQL, digamos que queremos descobrir:

1. A duração de nossa viagem em dias.
2. A duração de nosso vôo de nossa casa em Nova Iorque até a cidade de destino.
3. A data em que precisamos começar a nos preparar para a viagem, que podemos imaginar que seja em torno de 14 dias.

Queremos esta informação para cada viagem em nossa mesa. Veja aqui como encontrá-la usando o PostgreSQL:

SELECT 
  destination, 
  return_date - departure_date + 1 AS trip_duration, 
  arrival_timestamp - departure_timestamp AS flight_duration,
  departure_date - INTERVAL '14 DAYS' AS prep_date
FROM trips;

Note que adicionamos +1 para incluir tanto os dias de partida quanto de chegada à nossa duração da viagem. Aqui estão os resultados:

Há muito mais coisas que você pode fazer com data e hora no PostgreSQL. Na tabela seguinte, eu resumi como fazer:

• Obter a data e a hora atuais.
• Subtrair datas/horas.
• Adicionar/subtrair intervalos.
• Extrair certas partes da data/hora.
• Criar uma data/hora a partir de suas partes.
• Truncar datas.
• Converter cadeias de caracteres em objetos de data/hora.
• Transformar objetos de data/hora em cadeias de caracteres com formatação específica.

Para mais detalhes sobre cada uma das funções acima, consulte a documentação do PostgreSQL sobre tipos de data/hora, funções de data/hora e operadores, e funções de formatação de data/hora.

Funções de data e hora do Oracle

Aqui estão os tipos de dados para processamento de datas e horas no banco de dados Oracle:

• DATE. Este tipo de dados armazena informações de data e hora, incluindo século, ano, mês, data, hora, minuto e segundo. Se um componente de tempo não for especificado, então a hora padrão é meia-noite.
• O TIMESTAMP O tipo de dados é uma extensão do tipo de dados DATE, pois também armazena segundos fracionários.
  • TIMESTAMP WITH TIME ZONE é uma variante do tipo de dados TIMESTAMP. Ela armazena uma compensação de fuso horário ou nome da região do fuso horário.
  • TIMESTAMP WITH LOCAL TIME ZONE é outra variante do TIMESTAMP. Em vez de armazenar um offset de fuso horário como parte dos dados da coluna, a informação do fuso horário é simplesmente normalizada para o fuso horário do banco de dados, ou seja, a Oracle a devolve no fuso horário da sessão local dos usuários.
• O INTERVAL YEAR TO MONTH tipo de dado armazena um período de tempo usando o YEAR e MONTH
• O INTERVAL DAY TO SECOND O tipo de dado armazena um período de tempo em termos de dias, horas, minutos e segundos.

Você pode ler mais sobre os tipos de dados de data e hora da Oracle na documentação da Oracle; estamos passando para nosso caso de uso prático. Vamos agora criar o trips tabela no banco de dados Oracle.

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp with time zone,
    arrival_timestamp timestamp with time zone
);
   
INSERT INTO trips
VALUES (1, 'San Francisco', DATE'2022-03-21', DATE'2022-03-26', TIMESTAMP'2022-03-21 8:00:00 -04:00', TIMESTAMP'2022-03-21 11:14:00 -07:00');

INSERT INTO trips
VALUES (2, 'London', DATE'2022-06-03', DATE'2022-06-10', TIMESTAMP'2022-06-03 19:00:00 -04:00', TIMESTAMP'2022-06-04 07:10:00 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', DATE'2022-07-28', DATE'2022-08-10', TIMESTAMP'2022-07-28 15:00:00 -04:00', TIMESTAMP'2022-07-30 06:55:00 +10:00');

Como você pode ver, estamos usando o tipo de dados DATE para nossas datas de partida e retorno. (Aqui não precisamos de informações de fuso horário ou precisão até segundos fracionários). Estamos usando o TIME WITH TIME ZONE para nossas horas de partida e chegada, pois temos dados em diferentes fusos horários.

Observe que quando estamos inserindo valores, especificamos o tipo de dados para cada valor. Alternativamente, poderíamos usar o formato padrão Oracle sem especificar o tipo de dados (por exemplo, '21-MAR-2022' e '21-MAR-2022 8.00.00 AM -04.00'). Você pode alterar os formatos padrão usando os parâmetros de inicialização NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, e NLS_TIMESTAMP_TZ_FORMAT. Você pode encontrar mais informações na documentação da Oracle.

Para cada destino, queremos novamente descobrir a duração da viagem em dias (incluindo dias de partida e chegada), a duração do vôo para a cidade de destino, e a data em que precisamos começar a nos preparar para a viagem, assumindo que queremos começar 14 dias antes.

Veja aqui como encontrar esta informação no Oracle:

SELECT
  destination, 
  return_date - departure_date + 1 AS trip_duration, 
  arrival_timestamp - departure_timestamp AS flight_duration,
  departure_date - 14 AS prep_date
FROM trips;

Há muito mais coisas que você pode fazer com datas e horários no Oracle. A tabela a seguir resume como fazer:

• Obtenha a data e a hora atuais.
• Subtrair datas e horas.
• Adicionar/subtrair intervalos.
• Extrair certas partes da data/hora.
• Datas truncadas.
• Converter cadeias de caracteres em objetos de data/hora.
• Transformar objetos de data/hora em cadeias de caracteres com formatação específica.

Para mais detalhes sobre cada uma das funções acima - e algumas outras funções úteis de data e hora no Oracle - confira este artigo e a documentação do Oracle.

Funções de data e hora do SQLite

SQLite não possui tipos particulares de dados para armazenamento de datas e horas. Entretanto, as funções de dados e horários SQLite podem ajudá-lo a armazenar datas e horários como TEXT, REAL, ou INTEGER valores:

• TEXT como cordas ISO 8601 ('YYYY-MM-DD HH:MM:SS.SSS').
• REAL como o número de dias desde o meio-dia em Greenwich, em 24 de novembro de 4714 a.C.
• INTEGER como o número de segundos desde 1970-01-01 00:00:00 UTC.

Aqui estão as funções de data e hora da SQLite:

• O date() A função retorna a data no formato AAAA-MM-DD.
• O time() retorna o tempo no formato HH:MM:SS.
• O datetime() retorna o carimbo da hora no formato YYYY-MM-DD HH:MM:SS.
• O julianday() função retorna o Julian Day, ou seja, o número de dias desde o meio-dia em Greenwich, Inglaterra, em 24 de novembro de 4714 a.C.
• O strftime() retorna a data formatada de acordo com a seqüência de formatos especificada como o primeiro argumento.

Para criar nossa tabela de viagens na SQLite, podemos usar o seguinte código:

CREATE TABLE trips (
    id int,
    destination text,
    departure_date text,
    return_date text,
    departure_timestamp text,
    arrival_timestamp text
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Note que utilizamos o tipo de dados TEXT para armazenar todas as nossas datas e horas. Então, inserimos valores especificando datas e horas no formato usual, mesmo incluindo fusos horários quando necessário.

Agora queremos praticar as funções de data e hora SQLite calculando a duração de nossa viagem em dias (incluindo dias de partida e chegada), a duração do vôo em horas, e a data em que precisamos começar a nos preparar para a viagem, que é o habitual 14 dias fora.

Veja aqui como calculá-las no SQLite:

SELECT
  destination,
  julianday(return_date) - julianday(departure_date) + 1 AS trip_duration,
  (julianday(arrival_timestamp) - julianday(departure_timestamp)) * 24 AS flight_duration,
  date(departure_date, '-14 days') AS prep_date
FROM trips;

Nesta consulta, utilizamos a função julianday() ao calcular a diferença entre duas datas/horários. Esta função retorna o número de dias, que é o que esperamos para a duração da viagem. A duração do vôo é melhor apresentada em horas - assim, multiplicamos o resultado por 24 para obter a duração do vôo em horas. Observe também como a função date() nos permite adicionar/subtrair dias a partir de um valor de data. Você pode adicionar/subtrair anos, meses, horas, minutos e segundos de forma semelhante.

Vamos ver o que mais podemos fazer com datas e horários no SQLite. Na tabela a seguir, eu resumi como fazer:

• Obtenha a data atual e a hora atual.
• Subtraia as datas.
• Subtrair horas.
• Adicionar/subtrair intervalos.
• Extrair certas partes da data/hora.
• Datas truncadas.
• Converter objetos de data/hora em cordas com formatação específica.

Em contraste com as tabelas que temos para outros dialetos SQL, aqui as colunas não correspondem a tipos de dados específicos. Elas simplesmente incluem exemplos para processamento (1) de datas, (2) horas e datas e horas juntas (ou seja, carimbos de data/hora).

Para mais detalhes sobre as funções de data e hora do SQLite, verifique a documentação do SQLite.

Funções de data e hora do MySQL

Há cinco tipos de dados para processar datas e horários no MySQL:

• O DATE O tipo de dado é usado para valores com uma parte de data mas sem parte de tempo.
• O DATETIME O tipo de dados é usado para valores que contenham tanto peças de data como de hora. Você também pode ter segundos fracionários ao inserir os valores DATETIME na tabela. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
• O TIMESTAMP O tipo de dados também é usado para valores que contenham tanto peças de data como de hora. Ele também suporta segundos fracionários. Além disso, a partir do MySQL 8.0.19, este tipo de dado fornece suporte completo para informações de fuso horário. A faixa suportada é '1970-01-01 00:00:01' UTC até '2038-01-19 03:14:07' UTC.
• O TIME O tipo de dados é usado para representar uma hora do dia e também o tempo decorrido ou um intervalo de tempo entre dois eventos. TIME valores podem variar de '-838:59:59' a '838:59:59'. Os segundos fracionários são suportados.
• O YEAR O tipo de dados é usado para representar valores anuais. A faixa suportada é 1901 a 2155. Observe também que YEAR aceita valores em diversos formatos, por exemplo '2021', 2021, '21', 21.

Você pode ler mais sobre dados e tipos de dados de tempo no MySQL aqui.

Agora vamos repetir nosso exemplo com viagens, mas desta vez no MySQL. Começamos criando o trips mesa:

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp timestamp,
    arrival_timestamp timestamp
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00:00-04:00', '2022-03-21 11:14:00-07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00:00-04:00', '2022-06-04 07:10:00+01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00:00-04:00', '2022-07-30 06:55:00+10:00');

Como você pode ver, usamos o tipo de dados DATE para armazenar as datas de partida e retorno. Para as horas de partida e chegada, escolhemos o tipo de dados TIMESTAMP, pois queremos preservar as informações sobre o fuso horário.

Para calcular a duração da viagem (incluindo dias de partida e chegada), a duração do vôo e a data de início da preparação (14 dias de antecedência), podemos utilizar a seguinte consulta MySQL:

SELECT
    destination, 
    DATEDIFF(return_date, departure_date) + 1 AS trip_duration, 
    TIMEDIFF(arrival_timestamp, departure_timestamp) AS flight_duration,
    DATE_SUB(departure_timestamp, INTERVAL 14 DAY) AS prep_date
FROM trips;

Como você pode ver a partir desta consulta, existem algumas funções MySQL muito úteis que processam datas e horários. Na verdade, existem muitas dessas funções. Para ter uma idéia do que você pode fazer com datas e horas no MySQL, consulte a tabela a seguir e aprenda como fazê-lo:

• Obtenha a data e a hora atuais.
• Subtraia as datas e horas.
• Adicionar/subtrair intervalos.
• Extrair certas partes da data/hora.
• Crie uma data/hora a partir de suas partes.
• Converter cadeias de caracteres em objetos de data/hora.
• Modificar a formatação dos objetos de data/hora.

Para mais detalhes sobre cada uma das funções acima, consulte a documentação do MySQL sobre funções de data e hora.

Funções de data e hora do T-SQL

Finalmente, vamos cobrir as funções de data e hora que você deve saber se trabalha no MS SQL Server e usa o Transact-SQL (T-SQL).

Primeiro de tudo, aqui estão os tipos de dados de data e hora suportados pelo T-SQL:

• O time O tipo de dados é usado para armazenar valores de tempo, incluindo segundos fracionários.
• O date O tipo de dados é usado para armazenar valores de data sem peças de tempo.
• O smalldatetime O tipo de dados é usado no T-SQL para armazenar datas e horários na faixa de 1900-01-01 até 2079-06-06, com uma precisão de até 1 minuto.
• O datetime O tipo de dados pode armazenar datas e horários na faixa de 1753-01-01 até 9999-12-31, com uma precisão de até 0,00333 segundos.
• O T-SQL também tem o datetime2 tipo de dados. Ele armazena valores em uma faixa ainda mais ampla (0001-01-01 00:00:00.0000000 até 9999-12-31 23:59:59.9999999) e define segundos fracionais de até 100 nanossegundos.
• Finalmente, o datetimeoffset O tipo de dados armazena datas e horas com o fuso horário compensado. Ele tem o mesmo alcance e precisão do tipo de dados datetime2.

Você pode ler mais sobre os tipos de dados de data e hora no Transact-SQL aqui.

E agora é hora de repetir nosso exemplo de viagem com o T-SQL. Como de costume, começamos criando a trips mesa:

CREATE TABLE trips (
    id int,
    destination varchar(32),
    departure_date date,
    return_date date,
    departure_timestamp datetimeoffset,
    arrival_timestamp datetimeoffset
);

INSERT INTO trips
VALUES (1, 'San Francisco', '2022-03-21', '2022-03-26', '2022-03-21 08:00 -04:00', '2022-03-21 11:14 -07:00');

INSERT INTO trips
VALUES (2, 'London', '2022-06-03', '2022-06-10', '2022-06-03 19:00 -04:00', '2022-06-04 07:10 +01:00');

INSERT INTO trips
VALUES (3, 'Sydney', '2022-07-28', '2022-08-10', '2022-07-28 15:00 -04:00', '2022-07-30 06:55 +10:00');

Note que usamos (1) o tipo de dados de data para armazenar datas de partida e retorno, e (2) o tipo de dados datetimeoffset para armazenar as horas de partida e chegada (para preservar as informações de fuso horário).

Agora vamos calcular a duração da viagem (incluindo dias de partida e chegada), a duração do vôo, e a data para iniciar a preparação (14 dias de antecedência) usando o T-SQL:

SELECT
    destination, 
    DATEDIFF(day, departure_date, return_date) + 1 AS trip_duration, 
    DATEDIFF(hour, departure_timestamp, arrival_timestamp) AS flight_duration,
    DATEADD(day, -14, departure_timestamp) AS prep_date
FROM trips;

Como você pode ver neste exemplo, o T-SQL tem a função DATEDIFF() que processa não apenas datas, mas também valores de hora e data/hora. Entretanto, ao calcular a duração do vôo, conseguimos obter apenas o número inteiro de horas em vez do intervalo exato com minutos. Se você precisar de informações mais precisas, você pode sempre selecionar outra parte de data para esta função (por exemplo, minuto para obter a duração do vôo em minutos). Leia este artigo para ver como você pode processar a saída desta função para obter o intervalo no formato requerido.

A tabela a seguir resume algumas outras operações que você pode fazer com datas e horários em T-SQL:

• Obtenha a data e a hora atuais.
• Subtraia as datas e horas.
• Adicionar/subtrair intervalos.
• Extrair certas partes da data/hora (por exemplo, mês, minuto).
• Criar data/hora a partir de suas partes.
• Converter cadeias de caracteres em objetos de data/hora.
• Alterar os formatos de data e hora.

Como você pode ver neste exemplo, T-SQL tem a função DATEDIFF() que processa não apenas datas, mas também valores de hora e data/hora. Entretanto, ao calcular a duração do vôo, conseguimos obter apenas o número inteiro de horas ao invés do intervalo exato com minutos. Se você precisar de informações mais precisas, você pode sempre selecionar outra parte de data para esta função (por exemplo, minuto para obter a duração do vôo em minutos). Leia este artigo para ver como você pode processar a saída desta função para obter o intervalo no formato requerido.

A tabela a seguir resume algumas outras operações que você pode fazer com datas e horários em T-SQL:

• Obtenha a data e a hora atuais.
• Subtraia as datas e horas.
• Adicionar/subtrair intervalos.
• Extrair certas partes da data/hora (por exemplo, mês, minuto).
• Criar data/hora a partir de suas partes.
• Converter cadeias de caracteres em objetos de data/hora.
• Alterar os formatos de data e hora.

Para mais detalhes sobre as funções de data e hora do SQL Server, consulte a documentação do T-SQL.

Vamos praticar as funções de data e hora do SQL Server!

Espero que este artigo tenha fornecido a você um entendimento geral de como datas e horas podem ser processadas através de diferentes dialetos SQL. Agora é a hora de praticar!

Eu recomendo começar com um curso interativo. Você pode obter um conhecimento abrangente sobre dados SQL e funções de tempo, praticar estas funções com exemplos do mundo real, e obter alguma orientação quando estiver preso:

• Funções Comuns em SQL inclui 211 desafios de codificação. Você vai praticar as funções SQL padrão usadas no processamento de dados de texto, dados numéricos, datas e horas, e muito mais.
• Os tipos de dados em SQL incluem 89 exercícios interativos. Ele apresenta tipos de dados comuns no SQL Server, MySQL, Oracle, e PostgreSQL.

Se você deseja obter um conhecimento abrangente de SQL a partir da experiência zero de programação, recomendo a SQL de A a Z pista de aprendizagem. Inclui 7 cursos interativos que cobrem a escrita de consultas simples, combinando dados de várias tabelas, escrevendo consultas SQL complexas com subconsultas, e usando expressões comuns de tabelas, funções de janela, e muito mais.

Obrigado pela leitura, e feliz aprendizado.