Guia de Data e Hora no MySQL: Tipos de Dados, Funções, Formatação e Consultas por Intervalo

1. Introdução

MySQL é um sistema de gerenciamento de banco de dados amplamente usado para construir aplicações web e bancos de dados, e lidar corretamente com dados de data é especialmente importante. Por exemplo, dados relacionados a datas aparecem em muitos cenários: gerenciamento de posts de blog, acompanhamento do histórico de vendas de produtos e armazenamento do histórico de login de usuários. Ao gerenciar os dados de data corretamente, você pode processar as informações de forma eficiente e fornecer informações precisas aos usuários.

Neste guia, explicaremos de forma abrangente tudo, desde os tipos de dados de data/hora básicos e como usar funções de data no MySQL até cálculos baseados em datas e consultas por intervalo. Este conteúdo foi projetado para usuários iniciantes a intermediários, e incluiremos exemplos práticos de consultas baseados em casos de uso reais—tornando-o útil para o trabalho do dia a dia.

Ao ler este artigo, você será capaz de fazer o seguinte:

• Compreender as características dos tipos de dados de data/hora do MySQL e escolher o tipo adequado para seus dados.
• Usar funções de data para recuperar e manipular facilmente a data atual, datas passadas e datas futuras.
• Extrair dados para períodos específicos realizando buscas e comparações por intervalo de datas.

Agora, vamos analisar os fundamentos das datas no MySQL a partir da próxima seção.

2. Visão geral dos tipos de dados de data/hora do MySQL

Ao gerenciar datas no MySQL, é importante escolher o tipo de dado de data/hora correto dependendo dos dados e do caso de uso. O MySQL oferece vários tipos de dados para lidar com datas e horas, cada um com características e usos diferentes. Nesta seção, explicaremos detalhadamente os principais tipos de data/hora e seus casos de uso típicos.

DATE

O tipo DATE armazena apenas a data (ano, mês, dia) e não inclui componente de hora. O intervalo suportado vai de “1000-01-01” a “9999-12-31”, podendo lidar com datas do ano 1000 até 9999. É adequado para informações que exigem apenas a data, onde a hora não é relevante, como aniversários ou datas comemorativas.

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    birth_date DATE
);

TIME

O tipo TIME armazena hora (horas, minutos, segundos). O intervalo suportado vai de “-838:59:59” a “838:59:59”. Como pode representar tempos negativos, também pode ser usado para expressar diferenças de tempo. É útil para registrar horas de trabalho ou durações de tarefas.

CREATE TABLE work_log (
    id INT,
    task_name VARCHAR(50),
    duration TIME
);

DATETIME

O tipo DATETIME armazena data e hora. O intervalo suportado vai de “1000-01-01 00:00:00” a “9999-12-31 23:59:59”. Não depende de fusos horários, e você pode recuperar o valor armazenado exatamente como foi salvo. É adequado para registrar timestamps de eventos passados ou agendas futuras.

CREATE TABLE appointments (
    id INT,
    description VARCHAR(50),
    appointment_datetime DATETIME
);

TIMESTAMP

O tipo TIMESTAMP armazena data e hora e converte automaticamente os valores com base no fuso horário do servidor. O intervalo suportado vai de “1970-01-01 00:00:01 UTC” a “2038-01-19 03:14:07 UTC”. É compatível com o tempo de época Unix e adequado para registrar timestamps e histórico de alterações. Além disso, como o TIMESTAMP pode definir a hora atual por padrão, é conveniente para registrar automaticamente os tempos de criação e atualização.

CREATE TABLE posts (
    id INT,
    title VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

YEAR

O tipo YEAR armazena apenas o ano. O intervalo suportado vai de “1901” a “2155”. É útil quando se deseja representar um ano específico, como o ano de fabricação ou o ano de fundação.

CREATE TABLE products (
    id INT,
    name VARCHAR(50),
    manufactured_year YEAR
);

Comparação e casos de uso de cada tipo de data/hora

Resumo

Os tipos de dados de data/hora do MySQL devem ser escolhidos com base nas características dos seus dados e em como você pretende usá‑los. Na próxima seção, analisaremos mais de perto as diferenças entre DATETIME e TIMESTAMP, que são especialmente fáceis de confundir.

3. Diferenças entre DATETIME e TIMESTAMP

Ao trabalhar com datas e horas no MySQL, DATETIME e TIMESTAMP são comumente usados, mas há diferenças importantes entre eles. Ambos armazenam ano, mês, dia, hora, minuto e segundo, mas diferem na forma como tratam fusos horários e o intervalo de tempo que podem armazenar. Você deve escolher com base no seu caso de uso. Nesta seção, explicaremos as diferenças e características de DATETIME e TIMESTAMP em detalhes.

Características do DATETIME

• Não dependente de fusos horários : O tipo DATETIME não é afetado pela configuração de fuso horário do servidor. Você pode recuperar o valor armazenado exatamente como foi salvo.
• Intervalo : Ele suporta de 1000-01-01 00:00:00 até 9999-12-31 23:59:59.
• Casos de uso : Adequado para dados que você deseja armazenar sem depender de um fuso horário específico, como eventos históricos ou programações futuras.

Exemplo: Armazenando a data/hora de um evento

Por exemplo, se você quiser manter um valor de data/hora “universal” como está, DATETIME é uma boa escolha. O exemplo a seguir registra um evento agendado para uma data e hora específicas.

CREATE TABLE events (
    id INT,
    event_name VARCHAR(50),
    event_datetime DATETIME
);

Nesta tabela, a data/hora armazenada em event_datetime não é afetada pelos fusos horários, e o valor é recuperado exatamente como foi armazenado.

Características do TIMESTAMP

• Dependente de fusos horários : O tipo TIMESTAMP é armazenado e recuperado com base no fuso horário do servidor. Ao mover dados entre servidores em fusos horários diferentes, a conversão ocorrerá de acordo.
• Intervalo : Ele suporta de 1970-01-01 00:00:01 UTC até 2038-01-19 03:14:07 UTC (baseado no intervalo de tempo Unix).
• Suporte a atualização automática : Usando DEFAULT CURRENT_TIMESTAMP e ON UPDATE CURRENT_TIMESTAMP, o MySQL pode registrar automaticamente o timestamp atual ao inserir ou atualizar dados.
• Casos de uso : Ideal para rastrear automaticamente a hora de criação ou atualização, como quando você deseja que um timestamp mude sempre que um registro for atualizado.

Exemplo: Armazenando horários de criação e atualização de posts

Este exemplo usa o recurso de atualização automática do TIMESTAMP para registrar os horários de criação e atualização de posts de blog.

CREATE TABLE blog_posts (
    id INT,
    title VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Com esta configuração, created_at é registrado quando o post é criado pela primeira vez, e updated_at é atualizado automaticamente toda vez que o post é editado.

Tabela de Comparação: DATETIME vs TIMESTAMP

Como Escolher

• Escolha DATETIME se você não quiser efeitos de fuso horário : Por exemplo, se você quiser armazenar o horário de um evento como um horário local fixo para um país/região específico, DATETIME pode ser adequado.
• Escolha TIMESTAMP se precisar de atualização automática ou tratamento de fuso horário : Por exemplo, se você quiser registrar automaticamente quando linhas do banco de dados são atualizadas, TIMESTAMP é conveniente.

Resumo

DATETIME e TIMESTAMP têm características diferentes. Ao escolher o mais adequado para o seu propósito, você pode gerenciar dados de data/hora de forma mais eficiente. Na próxima seção, veremos as funções de data essenciais no MySQL.

4. Conceitos Básicos das Funções de Data/Hora do MySQL

O MySQL oferece muitas funções para trabalhar com datas e horas — desde recuperar a data/hora atual até adicionar ou subtrair intervalos. Essas funções são úteis para administração e análise de bancos de dados. Nesta seção, explicaremos os conceitos básicos das funções de data/hora do MySQL e seus casos de uso típicos.

Funções para Obter a Data/Hora Atual

Três funções comuns para obter a data e hora atuais no MySQL são NOW(), CURDATE() e CURTIME().

NOW()

A função NOW() retorna a data e hora atuais no formato YYYY-MM-DD HH:MM:SS. É útil para registro de logs e marcação de tempo de registros.

SELECT NOW(); -- Get the current date and time

CURDATE()

A função CURDATE() retorna a data atual no formato YYYY-MM-DD. Não inclui hora, portanto é adequada quando você precisa apenas da data.

SELECT CURDATE(); -- Get the current date

CURTIME()

A função CURTIME() retorna a hora atual no formato HH:MM:SS. Use-a quando precisar apenas da hora sem a data.

SELECT CURTIME(); -- Get the current time

Functions to Add/Subtract Dates

Para adicionar ou subtrair intervalos de uma data, use DATE_ADD() e DATE_SUB(). Essas funções facilitam o cálculo de datas futuras ou passadas.

DATE_ADD()

A função DATE_ADD() adiciona um intervalo especificado a uma data. Por exemplo, é útil para obter a data 7 dias depois ou 1 mês depois.

SELECT DATE_ADD('2023-01-01', INTERVAL 7 DAY); -- Returns 2023-01-08

DATE_SUB()

A função DATE_SUB() subtrai um intervalo especificado de uma data. Use-a para calcular datas passadas.

SELECT DATE_SUB('2023-01-01', INTERVAL 1 MONTH); -- Returns 2022-12-01

Function to Calculate Date Differences

Para calcular a diferença entre duas datas, DATEDIFF() é conveniente. Por exemplo, você pode calcular quantos dias se passaram desde uma data específica, ou o número de dias entre duas datas.

DATEDIFF()

A função DATEDIFF() retorna a diferença entre duas datas em dias. É útil quando você deseja confirmar o número de dias entre uma data de início e uma data final.

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Other Useful Date Functions

O MySQL oferece funções de data adicionais úteis.

EXTRACT()

A função EXTRACT() extrai uma parte específica (ano, mês, dia, etc.) de uma data. É útil quando você precisa apenas de uma parte da data.

SELECT EXTRACT(YEAR FROM '2023-01-01'); -- Extract year (2023)
SELECT EXTRACT(MONTH FROM '2023-01-01'); -- Extract month (1)
SELECT EXTRACT(DAY FROM '2023-01-01'); -- Extract day (1)

DATE_FORMAT()

A função DATE_FORMAT() exibe uma data em um formato especificado. É útil quando você deseja exibir datas em um formato específico (por exemplo, formatação ao estilo japonês).

SELECT DATE_FORMAT('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01

5. Date Formatting and Conversion Methods

No MySQL, você pode alterar a forma como as datas são exibidas para um formato mais legível, ou converter strings de data em tipos de data. Isso permite controlar flexivelmente os formatos de exibição e gerenciar os dados de forma consistente, mesmo quando as entradas vêm em formatos diferentes. Nesta seção, explicaremos as principais funções usadas para formatação e conversão de datas.

Date Formatting with the DATE_FORMAT() Function

Usando DATE_FORMAT(), você pode exibir valores de data em um formato especificado. Isso é especialmente útil quando você precisa de um formato de exibição diferente do padrão, como o formato japonês “YYYY年MM月DD日”.

Format Options

Com DATE_FORMAT(), você pode usar opções de formato como as seguintes:

• %Y : ano com 4 dígitos
• %y : ano com 2 dígitos
• %m : mês com 2 dígitos (01–12)
• %d : dia com 2 dígitos (01–31)
• %H : hora com 2 dígitos (00–23)
• %i : minuto com 2 dígitos (00–59)
• %s : segundo com 2 dígitos (00–59)

Example Usage

Por exemplo, para exibir a data 2023-01-01 como “2023年01月01日”, você pode escrever o seguinte:

SELECT DATE_FORMAT('2023-01-01', '%Y年%m月%d日'); -- Returns 2023年01月01日

Você também pode exibir uma data e hora com barras, como “2023/01/01 12:30:45”.

SELECT DATE_FORMAT('2023-01-01 12:30:45', '%Y/%m/%d %H:%i:%s'); -- Returns 2023/01/01 12:30:45

Convertendo Strings para Datas com STR_TO_DATE()

A função STR_TO_DATE() converte uma string de data em um tipo de data do MySQL. Por exemplo, é útil quando você deseja tratar uma string como “2023年01月01日” como um valor DATE.

Exemplo de Uso

Para converter a string “2023-01-01” em um valor DATE, escreva o seguinte:

SELECT STR_TO_DATE('2023-01-01', '%Y-%m-%d'); -- Returns 2023-01-01 as a DATE

Para converter uma string no estilo japonês como “2023年01月01日” em uma data, especifique o formato correspondente:

SELECT STR_TO_DATE('2023年01月01日', '%Y年%m月%d日'); -- Returns 2023-01-01 as a DATE

Exemplos de Conversão de Datas a partir de Diferentes Formatos

Na prática, os formatos de entrada de datas podem variar, portanto pode ser necessário converter a partir de diferentes formatos. Abaixo estão alguns exemplos.

1. Converter “YYYY/MM/DD” para um valor DATE

   SELECT STR_TO_DATE('2023/01/01', '%Y/%m/%d'); -- Returns 2023-01-01 as a DATE
   

2. Converter “MM-DD-YYYY” para um valor DATE

   SELECT STR_TO_DATE('01-01-2023', '%m-%d-%Y'); -- Returns 2023-01-01 as a DATE
   

Dessa forma, mesmo que os dados sejam inseridos em formatos diferentes, você pode armazená‑los e gerenciá‑los de maneira consistente em um formato de data unificado.

Diferenças entre DATE_FORMAT e STR_TO_DATE, e Quando Usar Cada Uma

• DATE_FORMAT() : usado para alterar o formato de exibição de valores de data existentes. Como a alteração afeta apenas a exibição, os dados armazenados não são modificados.
• STR_TO_DATE() : usado para converter uma string de data em um valor DATE ou DATETIME do MySQL. Essa conversão altera o tipo de dado armazenado e facilita o processamento de datas com outras funções e consultas do MySQL.

Resumo

Ao usar DATE_FORMAT() e STR_TO_DATE(), você pode gerenciar de forma flexível como os dados de data são exibidos e armazenados. Isso permite lidar com entradas de usuário ou do sistema de maneira mais adaptável, mantendo uma gestão de datas consistente. Na próxima seção, explicaremos cálculos e comparações de datas e analisaremos mais detalhadamente como calcular e comparar períodos usando dados de data.

6. Cálculos e Comparações de Datas

O MySQL oferece várias funções convenientes para cálculos e comparações de datas. Elas permitem extrair dados de períodos específicos ou calcular diferenças de datas para análise. Nesta seção, apresentaremos as principais funções usadas para cálculos e comparações de datas, além de como utilizá‑las.

Função para Calcular Diferenças de Datas: DATEDIFF()

A função DATEDIFF() devolve a diferença entre duas datas em “dias”. É útil quando você deseja verificar quantos dias se passaram entre datas ou determinar quantos dias se passaram desde um evento passado até hoje.

Exemplo de Uso

Por exemplo, para calcular o número de dias de 1 de janeiro de 2023 até 10 de janeiro de 2023, escreva o seguinte:

SELECT DATEDIFF('2023-01-10', '2023-01-01'); -- Returns 9

Neste exemplo, a diferença entre a data inicial e a data final é de 9 dias, portanto o resultado é 9.

Calculando Diferenças por Unidade com TIMESTAMPDIFF()

A função TIMESTAMPDIFF() calcula a diferença entre duas datas ou datetimes em uma unidade especificada (ano, mês, dia, hora, minuto, segundo). Por exemplo, você pode calcular diferenças em “meses” ou “horas”, conforme necessário.

Exemplo de Uso

1. Calcular uma diferença em meses

   SELECT TIMESTAMPDIFF(MONTH, '2022-01-01', '2023-01-01'); -- Returns 12
   

2. Calcular uma diferença em horas

   SELECT TIMESTAMPDIFF(HOUR, '2023-01-01 00:00:00', '2023-01-02 12:00:00'); -- Returns 36
   

Comparando Datas

As comparações de datas são realizadas usando os operadores padrão de comparação do MySQL (<, >, <=, >=, =). Isso permite extrair dados dentro de um período específico ou incluir datas passadas/futuras como condições.

Exemplo de Uso

Por exemplo, para extrair dados “em ou após 1 de janeiro de 2023”, escreva o seguinte:

SELECT * FROM events WHERE event_date >= '2023-01-01';

Especificando um Intervalo com BETWEEN

O operador BETWEEN permite especificar um intervalo de datas para recuperar dados. É útil quando você deseja extrair registros que se enquadram em um período específico.

Exemplo de Uso

Por exemplo, para recuperar dados de 1 de janeiro de 2023 a 31 de janeiro de 2023, escreva o seguinte:

SELECT * FROM events WHERE event_date BETWEEN '2023-01-01' AND '2023-01-31';

Cálculos de Data com ADDDATE() e SUBDATE()

Você pode usar ADDDATE() e SUBDATE() para adicionar ou subtrair um número de dias de uma data. Eles são convenientes para calcular datas futuras ou passadas.

Exemplo de Uso

1. Adicionar 10 dias a uma data

   SELECT ADDDATE('2023-01-01', 10); -- Returns 2023-01-11
   

2. Subtrair 10 dias de uma data

   SELECT SUBDATE('2023-01-01', 10); -- Returns 2022-12-22
   

Resumo

Ao usar os recursos de cálculo e comparação de datas do MySQL, você pode extrair dados de forma eficiente para períodos específicos e analisar os dados por intervalos de tempo. Na próxima seção, examinaremos mais de perto o tópico avançado de “buscas por intervalo de datas”.