Uma das soluções para extrair parcialmente uma informação temporal de campos de data e/ou hora no MySQL é usar funções apropriadas para extração de componetes (component-extraction functions).
Neste texto, vou mostrar como invocar funções específicas para extrair apenas a parte que interessa de um campo ou variável com date e hora, fazendo uso de funções, como MONTH(), MINUTE() etc.
Estas funções são mais eficientes (e rápidas) para extrair partes específicas de informações temporais do que as funções DATE_FORMAT() e TIME_FORMAT().
As funções do MySQL para extrair datas, horários ou partes de valores temporais
O MySQL inclui várias funções para extrair valores completos ou parciais de informações temporais.
As funções relacionadas a datas, trabalham com valores DATE, DATETIME ou TIMESTAMP. As informações relacionadas horários, trabalham com valores TIME, DATETIME ou TIMESTAMP.
Mesmo tendo opções dentro das linguagens de programação, com as quais você pode interagir com um banco de dados MySQL, estas funções internas podem tornar a extração de dados muito mais eficiente em seu sistema. Por isto vale a pena conhecê-las e usá-las.
| Função | Valor retornado |
|---|---|
YEAR() |
Extrai e retorna o ano da data informada |
MONTH() |
Extrai e retorna o número do mês a partir da data informada (1 a 12) |
MONTHNAME() |
Extrai e retorna o nome do mês (janeiro, fevereiro etc) |
DAYOFMONTH() |
day of month – extrai e retorna o dia do mês (1 a 31) |
DAYNAME() |
Extrai e retorna o nome do dia da semana (domingo, segunda, terça etc) |
DAYOFWEEK() |
Extrai e retorna o número do dia da semana – domingo é o dia 1; segunda é o dia 2; terça é o dia 3 etc. |
WEEKDAY() |
Extrai e retorna o número do dia da semana – domingo é o dia 0; segunda é o dia 1; terça é o dia 2 etc. |
DAYOFYEAR() |
Extrai e retorna o número do dia no ano (1 a 366) |
HOUR() |
Extrai e retorna o valor da hora (0 a 23) |
MINUTE() |
Extrai e retorna a quantidade de minutos (0 a 59) |
SECOND() |
Extrai e retorna a quantidade de segundos (0 a 59) |
No exemplo, abaixo, vamos determinar, a partir de sua data de nascimento, em que dia da semana o poeta Pablo Neruda nasceu:
SELECT DAYNAME('1904-07-12');
+-----------------------+
| DAYNAME('1904-07-12') |
+-----------------------+
| terça |
+-----------------------+
1 row in set (0.00 sec)
Você pode informar o nome de um campo (uma coluna) de uma tabela para qualquer uma destas funções. Veja como obter os nomes dos meses:
SELECT CidadeCliente, MONTHNAME(DataCadastroCliente) FROM CadastroClientes LIMIT 5;
+---------------+--------------------------------+ | CidadeCliente | MONTHNAME(DataCadastroCliente) | +---------------+--------------------------------+ | Tarbes | março | | Solingen | junho | | Pizzoferrato | março | | Monmouth | novembro | | Dunfermline | junho | +---------------+--------------------------------+ 5 rows in set (0.00 sec)
Se os nomes dos meses e das semanas não estiverem aparecendo em português, será necessário ajustar a variável ‘lc_time_names’. O artigo Como configurar o MySQL para exibir datas em português aborda este assunto.
Se quiser, é possível aninhar uma função dentro da outra. Veja:
SELECT DAYNAME(NOW()), DAYOFWEEK(NOW()), MONTHNAME(NOW()), YEAR(NOW());
+----------------+------------------+------------------+-------------+ | DAYNAME(NOW()) | DAYOFWEEK(NOW()) | MONTHNAME(NOW()) | YEAR(NOW()) | +----------------+------------------+------------------+-------------+ | terça | 3 | junho | 2015 | +----------------+------------------+------------------+-------------+ 1 row in set (0.00 sec)
Se quiser saber mais sobre a função NOW(), leia o artigo Como obter data e hora atuais no MySQL, onde esta e outras funções (CURDATE(), CURTIME() etc) são abordadas.
Funções como YEAR() ou DAYOFMONTH() extraem valores que possuem correspondência clara com o nome da função.
Por outro lado, algumas funções de extração de dados temporais resultam em valores cuja correspondência não é tão óbvia assim.
Uma delas é DAYOFYEAR (dia do ano):
SELECT DataCadastroCliente, DAYOFYEAR(DataCadastrocliente) FROM CadastroClientes WHERE YEAR(DataCadastroCliente)>=2016 ORDER BY DataCadastroCliente;
+---------------------+--------------------------------+ | DataCadastroCliente | DAYOFYEAR(DataCadastrocliente) | +---------------------+--------------------------------+ | 2016-01-02 11:26:05 | 2 | | 2016-01-07 01:43:07 | 7 | | 2016-02-20 00:38:10 | 51 | | 2016-02-21 13:37:11 | 52 | | 2016-03-29 10:03:39 | 89 | | 2016-04-04 10:09:20 | 95 | | 2016-04-14 21:03:31 | 105 | | 2016-04-16 09:17:54 | 107 | | 2016-04-30 09:27:05 | 121 | | 2016-05-12 03:19:44 | 133 | | 2016-06-11 09:43:57 | 163 | | 2016-06-30 07:42:35 | 182 | | 2016-07-06 04:16:31 | 188 | | 2016-07-08 12:39:24 | 190 | | 2016-07-25 01:32:53 | 207 | +---------------------+--------------------------------+ 15 rows in set (0.00 sec)
Como obter o nome do dia da semana ou do mês abreviado
Como não há funções específicas, no MySQL para obter (por exemplo) apenas as 3 primeiras letras do nome do dia da semana (dom, seg, ter etc.) ou do mês (jan, fev, mar etc.), você combinar as funções que já conhece com outras, que permitem manipular strings — neste caso, queremos obter substrings de outras strings.
Veja um exemplo:
SELECT DataCadastroCliente, LEFT(DAYNAME(DataCadastrocliente), 3), LEFT(MONTHNAME(DataCadastroCliente), 3) FROM CadastroClientes WHERE YEAR(DataCadastroCliente)>=2016 ORDER BY MONTHNAME(DataCadastroCliente);
+---------------------+---------------------------------------+-----------------------------------------+ | DataCadastroCliente | LEFT(DAYNAME(DataCadastrocliente), 3) | LEFT(MONTHNAME(DataCadastroCliente), 3) | +---------------------+---------------------------------------+-----------------------------------------+ | 2016-04-14 21:03:31 | qui | abr | | 2016-04-04 10:09:20 | seg | abr | | 2016-04-30 09:27:05 | sáb | abr | | 2016-04-16 09:17:54 | sáb | abr | | 2016-02-20 00:38:10 | sáb | fev | | 2016-02-21 13:37:11 | dom | fev | | 2016-01-02 11:26:05 | sáb | jan | | 2016-01-07 01:43:07 | qui | jan | | 2016-07-08 12:39:24 | sex | jul | | 2016-07-25 01:32:53 | seg | jul | | 2016-07-06 04:16:31 | qua | jul | | 2016-06-30 07:42:35 | qui | jun | | 2016-06-11 09:43:57 | sáb | jun | | 2016-05-12 03:19:44 | qui | mai | | 2016-03-29 10:03:39 | ter | mar | +---------------------+---------------------------------------+-----------------------------------------+ 15 rows in set (0.00 sec)
A função LEFT() é uma entre várias outras funções para manipular strings no MySQL.
Você pode ir além, com a função EXTRACT() — uma outra forma de obter partes de strings contendo valores temporais.
Veja um exemplo de uso:
SELECT EXTRACT(DAY FROM DataCadastroCliente) AS "Dia", EXTRACT(MONTH FROM DataCadastroCliente) AS "Mês", EXTRACT(YEAR FROM DataCadastroCliente) AS "Ano" FROM CadastroClientes WHERE MONTH(DataCadastroCliente)=8;
+------+------+------+ | Dia | Mês | Ano | +------+------+------+ | 31 | 8 | 2015 | | 28 | 8 | 2012 | | 10 | 8 | 2013 | | 3 | 8 | 2015 | | 21 | 8 | 2012 | | 2 | 8 | 2014 | | 13 | 8 | 2013 | | 28 | 8 | 2012 | | 7 | 8 | 2012 | +------+------+------+ 9 rows in set (0.00 sec)
A função EXTRACT() aceita unidades especificadoras, como YEAR, MONTH, DAY, HOUR, MINUTE ou SECOND.
