Resumo : neste tutorial, você aprenderá sobre DATETIMEo tipo de dados MySQL e como usar algumas funções úteis para uma manipulação DATETIME eficaz.
Introdução ao tipo de dados MySQL DATETIME
O tipo de dados MySQL DATETIMEpermite armazenar um valor que contém data e hora .
Quando você consulta dados de uma DATETIMEcoluna, o MySQL exibe o DATETIMEvalor no seguinte formato:
'YYYY-MM-DD HH:MM:SS'Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Ao inserir um valor em uma DATETIMEcoluna, você usa o mesmo formato. Por exemplo:
INSERT INTO table_name(datetime_column)
VALUES('2023-12-31 15:30:45');Linguagem de código: JavaScript ( javascript )Para preencher uma coluna com a data e hora atuais, use o resultado da função CURRENT_TIMESTAMPou NOW()como valor padrão. Por exemplo:
CREATE TABLE events(
id INT AUTO_INCREMENT PRIMARY KEY,
event_name VARCHAR(255) NOT NULL,
started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO events(event_name)
VALUES('Connected to MySQL Server');
SELECT * FROM events;Linguagem de código: PHP ( php )Saída:
+----+---------------------------+---------------------+
| id | event_name | started_at |
+----+---------------------------+---------------------+
| 1 | Connected to MySQL Server | 2023-12-28 07:51:18 |
+----+---------------------------+---------------------+
1 row in set (0.00 sec)Linguagem de código: JavaScript ( javascript )Por padrão, DATETIME os valores variam de 1000-01-01 00:00:00até 9999-12-31 23:59:59. MySQL usa 5 bytes para armazenar um DATETIMEvalor.
Além disso, um DATETIMEvalor pode incluir uma fração de segundo final até microssegundos com o formato, YYYY-MM-DD HH:MM:SS[.fraction]por exemplo, 2015-12-20 10:01:00.999999.
Ao incluir a precisão de segundos fracionários, DATETIMEos valores exigem mais armazenamento, conforme ilustrado na tabela a seguir:
| Precisão de segundos fracionários | Armazenamento (Bytes) |
|---|---|
| 0 | 0 |
| 1, 2 | 1 |
| 3, 4 | 2 |
| 5, 6 | 3 |
Por exemplo, 2015-12-20 10:01:00.999999 requer 8 bytes, 5 bytes para 2015-12-20 10:01:00 e 3 bytes para .999999 , enquanto 2015-12-20 10:01:00.9 requer apenas 6 bytes, 1 byte para a precisão do segundo fracionário.
MySQL DATETIME vs.TIMESTAMP
O MySQL fornece outro tipo de dados temporais semelhante ao DATETIMEchamado TIMESTAMP.
O TIMESTAMPrequer 4 bytes enquanto DATETIMErequer 5 bytes. Ambos requerem bytes adicionais para precisão de segundos fracionários TIMESTAMP.DATETIME
TIMESTAMPos valores variam de 1970-01-01 00:00:01 UTCaté 2038-01-19 03:14:07 UTC. Se você deseja armazenar valores temporais posteriores a 2038, você deve usar DATETIMEem vez de TIMESTAMP.
MySQL armazena TIMESTAMPem valor UTC. No entanto, o MySQL armazena o DATETIMEvalor como está, sem fuso horário. Vejamos o exemplo a seguir.
Primeiro, defina o fuso horário da conexão atual como +00:00.
SET time_zone = '+00:00';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A seguir, crie uma tabela chamada timestamp_n_datetimeque consiste em duas colunas: tse dtcom TIMESTAMPe DATETIMEtipos usando a seguinte instrução.
CREATE TABLE timestamp_n_datetime (
id INT AUTO_INCREMENT PRIMARY KEY,
ts TIMESTAMP,
dt DATETIME
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Em seguida, insira a data e hora atuais em ambas tsas dt colunas da timestamp_n_datetimetabela,
INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Depois disso, consulte os dados da timestamp_n_datetimetabela.
SELECT
ts,
dt
FROM
timestamp_n_datetime;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Os valores nas colunas DATETIMEe TIMESTAMPsão iguais.
Por fim, defina o fuso horário da conexão +03:00e consulte os dados da timestamp_n_datetimetabela novamente.
SET time_zone = '+03:00';
SELECT
ts,
dt
FROM
timestamp_n_datetime;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
A saída indica que o valor na TIMESTAMPcoluna é diferente. Isso ocorre porque a TIMESTAMPcoluna armazena o valor de data e hora em UTC quando alteramos o fuso horário, o valor da TIMESTAMPcoluna é ajustado de acordo com o novo fuso horário.
Isso significa que se você usar os TIMESTAMPdados para armazenar valores de data e hora, deverá levar seriamente em consideração ao mover seu banco de dados para um servidor localizado em um fuso horário diferente.
Funções MySQL DATETIME
A instrução a seguir define a variável @dtpara a data e hora atuais usando a NOW()função.
SET @dt = NOW();Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Para consultar o valor da @dtvariável, você usa a seguinte SELECTinstrução:
SELECT @dt;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Função MySQL DATA()
Para extrair a parte da data de um DATETIMEvalor, use a DATEfunção da seguinte forma:
SELECT DATE(@dt);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Esta função é muito útil caso você queira consultar dados com base em uma data, mas os dados armazenados na coluna são baseados em data e hora.
Vejamos o exemplo a seguir.
CREATE TABLE test_dt (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at DATETIME
);
INSERT INTO test_dt(created_at)
VALUES('2015-11-05 14:29:36');Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Suponha que você queira saber em qual linha foi criada 2015-11-05, você usa a seguinte consulta:
SELECT
*
FROM
test_dt
WHERE
created_at = '2015-11-05';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Ele não retorna nenhuma linha.
Isso ocorre porque a created_atcoluna contém não apenas a data, mas também a hora. Para corrigi-lo, você usa a DATEfunção da seguinte forma:
SELECT
*
FROM
test_dt
WHERE
DATE(created_at) = '2015-11-05';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Ele retorna uma linha conforme esperado. Caso a tabela tenha muitas linhas, o MySQL deve realizar uma varredura completa da tabela para localizar as linhas que correspondem à condição.
Função MySQL TIME
Para extrair a parte do tempo de um DATETIMEvalor, use a TIMEfunção como a seguinte instrução:
SELECT TIME(@dt);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Funções MySQL ANO, TRIMESTRE, MÊS, SEMANA, DIA, HORA, MINUTO e SEGUNDO
Para obter o ano, trimestre, mês, semana, dia, hora, minuto e segundo de um DATETIMEvalor, você usa as funções mostradas na instrução a seguir:
SELECT
HOUR(@dt),
MINUTE(@dt),
SECOND(@dt),
DAY(@dt),
WEEK(@dt),
MONTH(@dt),
QUARTER(@dt),
YEAR(@dt);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Função MySQL DATE_FORMAT
Para formatar um DATETIMEvalor, você usa a DATE_FORMATfunção. Por exemplo, a instrução a seguir formata um DATETIMEvalor com base no %H:%i:%s - %W %M %Y formato:
SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Função MySQL DATE_ADD
Para adicionar um intervalo a um DATETIMEvalor, você usa DATE_ADDa função da seguinte forma:
SELECT @dt start,
DATE_ADD(@dt, INTERVAL 1 SECOND) '1 second later',
DATE_ADD(@dt, INTERVAL 1 MINUTE) '1 minute later',
DATE_ADD(@dt, INTERVAL 1 HOUR) '1 hour later',
DATE_ADD(@dt, INTERVAL 1 DAY) '1 day later',
DATE_ADD(@dt, INTERVAL 1 WEEK) '1 week later',
DATE_ADD(@dt, INTERVAL 1 MONTH) '1 month later',
DATE_ADD(@dt, INTERVAL 1 YEAR) '1 year later';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Função MySQL DATE_SUB
Para subtrair um intervalo de um DATETIMEvalor, você usa DATE_SUBa função da seguinte forma:
SELECT @dt start,
DATE_SUB(@dt, INTERVAL 1 SECOND) '1 second before',
DATE_SUB(@dt, INTERVAL 1 MINUTE) '1 minute before',
DATE_SUB(@dt, INTERVAL 1 HOUR) '1 hour before',
DATE_SUB(@dt, INTERVAL 1 DAY) '1 day before',
DATE_SUB(@dt, INTERVAL 1 WEEK) '1 week before',
DATE_SUB(@dt, INTERVAL 1 MONTH) '1 month before',
DATE_SUB(@dt, INTERVAL 1 YEAR) '1 year before';Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Função MySQL DATE_DIFF
Para calcular a diferença em dias entre dois DATETIMEvalores, você usa a DATEDIFFfunção. Observe que a DATEDIFFfunção considera apenas a data como parte de um DATETIMEvalor no cálculo.
Veja o exemplo a seguir.
Primeiro, crie uma tabela chamada datediff_testque possua uma coluna cujo tipo de dados seja DATETIME.
CREATE TABLE datediff_test (
dt DATETIME
);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Segundo, insira algumas linhas na datediff_testtabela.
INSERT INTO datediff_test(dt)
VALUES('2010-04-30 07:27:39'),
('2010-05-17 22:52:21'),
('2010-05-18 01:19:10'),
('2010-05-22 14:17:16'),
('2010-05-26 03:26:56'),
('2010-06-10 04:44:38'),
('2010-06-13 13:55:53');Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Terceiro, use a DATEDIFFfunção para comparar a data e hora atuais com o valor em cada linha da datediff_testtabela.
SELECT
dt,
DATEDIFF(NOW(), dt)
FROM
datediff_test;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Neste tutorial, você aprendeu sobre DATETIMEo tipo de dados MySQL e algumas DATETIMEfunções úteis.
