Como manipular valores de data e hora como números no MySQL

Transformar strings contendo valores temporais (de data e/ou hora) pode ser útil quando se deseja realizar operações aritméticas, envolvendo-os. Os valores podem ser convertidos de volta para data e/ou hora, com relativa facilidade.
O processo consiste, basicamente em adicionar o valor zero ou usar o valor temporal em um contexto numérico — o MySQL irá entender que aquele valor é um número.
Veja um exemplo:

SELECT CURRENT_DATE() AS Hoje, CURRENT_DATE()+0 AS "Hoje, como núm.";
+------------+------------------+
| Hoje       | Hoje, como núm.  |
+------------+------------------+
| 2015-07-14 |         20150714 |
+------------+------------------+

CURRENT_DATE() é a função responsável por retornar a data atual. Como você pode ver, no exemplo acima, ao adicionar 0 (zero), o valor passou de data para número.
Na condição de valor numérico, é possível realizar todas as operações aritméticas e, em seguida, voltar (nem sempre…) a valor temporal.
Veja outro exemplo:

SELECT DATE(DataCadastroCliente) AS Cadastro, DATE(DataCadastroCliente)+0 AS "Número", YEAR(DataCadastroCliente)+1 AS "Ano +1" FROM CadastroClientes LIMIT 10;
+------------+----------+--------+
| Cadastro   | Número   | Ano +1 |
+------------+----------+--------+
| 2016-03-29 | 20160329 |   2017 |
| 2015-06-20 | 20150620 |   2016 |
| 2013-03-29 | 20130329 |   2014 |
| 2015-11-29 | 20151129 |   2016 |
| 2016-06-11 | 20160611 |   2017 |
| 2014-06-14 | 20140614 |   2015 |
| 2015-08-31 | 20150831 |   2016 |
| 2012-08-28 | 20120828 |   2013 |
| 2013-05-29 | 20130529 |   2014 |
| 2013-06-03 | 20130603 |   2014 |
+------------+----------+--------+

mysql-convert-datetime-to-number
Fique atento.
Ao adicionar 0 (ou qualquer outro valor numérico) a um valor temporal, este deixa de ter valor temporal e passa a ser uma simples string numérica.
Essencialmente, você deve esperar, como resultado, uma string sem os delimitadores de data.
Mas apenas o primeiro componente da data ou da hora será interpretado como numérico, durante a conversão. Veja por si só:

SELECT '2016-01-01'+0, '2016-01-01 15:45:25'+0, '15:45:25'+0;
+----------------+-------------------------+--------------+
| '2016-01-01'+0 | '2016-01-01 15:45:25'+0 | '15:45:25'+0 |
+----------------+-------------------------+--------------+
|           2016 |                    2016 |           15 |
+----------------+-------------------------+--------------+

Para resolver isto, use as funções SEC_TO_TIME() e TIME_TO_SEC().

Como determinar o primeiro e o último dia do mês no MySQL

Dada uma data, você deseja determinar qual o primeiro dia da semana naquele mês ou qual o último dia do mês (28, 29, 30 ou 31).
Veja algumas abordagens para este problema — e use a que melhor lhe convier.
Se você conhece outra solução, dentro do MySQL, sinta-se à vontade para expôr nos comentários.
Logo MySQL - The Dolphin.
Abaixo, veja como resolver o problema usando uma combinação das funções DATE_SUB() e DAYOFMONTH(). Neste caso

SELECT '2016-07-19', DATE_SUB('2016-07-19', INTERVAL DAYOFMONTH('2016-07-19')-1 DAY) AS 'Primeiro dia do mês';
+------------+----------------------+
| 2016-07-19 | Primeiro dia do mês  |
+------------+----------------------+
| 2016-07-19 | 2016-07-01           |
+------------+----------------------+

Obviamente, o primeiro dia de um mês é sempre o dia 01.
Faz mais sentido determinar em que dia da semana cai o primeiro dia do mês. isto pode ser feito com o acréscimo da função DAYOFWEEK():

SELECT '2016-07-19', DAYOFWEEK(DATE_SUB('2016-07-19', INTERVAL DAYOFMONTH('2016-07-19')-1 DAY)) AS 'Primeiro dia do mês';
+------------+----------------------+
| 2016-07-19 | Primeiro dia do mês  |
+------------+----------------------+
| 2016-07-19 |                    6 |
+------------+----------------------+

Sabendo que Domingo é o dia 1, chega-se à conclusão de que o dia 6 é Sexta-feira.
Com a função DAYNAME() fica mais fácil obter esta informação, veja:

SELECT '2016-07-19', DAYNAME(DATE_SUB('2016-07-19', INTERVAL DAYOFMONTH('2016-07-19')-1 DAY)) AS 'Primeiro dia do mês';
+------------+----------------------+
| 2016-07-19 | Primeiro dia do mês  |
+------------+----------------------+
| 2016-07-19 | sexta                |
+------------+----------------------+

A abordagem, que vimos é um tanto complexa e serve apenas para demonstrar algumas funções de manipulação das informações de data do MySQL.
Veja, a seguir, algumas maneiras mais rápidas de obter suas respostas.

Como estabelecer o último dia do mês no MySQL

A função LAST_DAY() pode dar este resultado num piscar de olhos. Sua sintaxe é simples e pede apenas uma data:

SELECT LAST_DAY('2016-02-15') AS 'Últ. dia do mês';
+-------------------+
| Últ. dia do mês   |
+-------------------+
| 2016-02-29        |
+-------------------+

Esta é uma maneira fácil de saber quantos dias o mẽs de Fevereiro terá em um determinado ano.
Você pode aninhar funções e descobrir um pouco mais — por exemplo, em qual dia da semana, cai esta data:

SELECT DAYNAME(LAST_DAY('2016-02-15')) AS 'Últ. dia do mês';
+-------------------+
| Últ. dia do mês   |
+-------------------+
| segunda           |
+-------------------+

A função LAST_DAY() também pode ser usada para determinar a extensão do mês ou o que falta para concluí-lo a partir de uma determinada data.

Como usar a função LAST_DAY para determinar qual o primeiro dia do mês

Ainda não existe a função FIRST_DAY(), no MySQL. E, de certa forma, ela é desnecessária.
O que varia, de mês para mês, é o último dia — que pode ser 28, 29, 30 ou 31.
O primeiro dia de cada mês, é sempre o dia 01 — mas o dia da semana varia, conforme demonstramos no começo deste artigo.
Tecnicamente, o primeiro dia de cada mês é sempre o dia posterior à data retornada por LAST_DAY(), referente ao mês anterior. Por exemplo: LAST_DAY('uma data do mẽs de Março')+1 = primeiro dia do mẽs de Abril.
Veja esta outra solução para determinar o primeiro dia de um determinado mẽs:

SELECT CURDATE( ) AS 'Hoje', ADDDATE(LAST_DAY(SUBDATE(CURDATE(), INTERVAL 1 MONTH)), 1) AS 'Primeiro dia do mês';
+------------+----------------------+
| Hoje       | Primeiro dia do mês  |
+------------+----------------------+
| 2015-07-12 | 2015-07-01           |
+------------+----------------------+

Veja o que fizemos no exemplo acima:

  • subtraímos um mês ao resultados de CURDATE() Para obter o mesmo dia do mês anterior
  • A partir daí usamos a função LAST_DAY() para estabelecer o último dia daquele mês
  • Finalmente, a função ADDDATE() é usada para adicionar um dia ao resultado — o que nos dá o primeiro dia do mẽs subsequente (que é o mês atual)

Este método pode ser usado, sem perigo de obter resultados indesejados, quando datas entre Dezembro e Janeiro de outro ano, estiverem envolvidas na conta.
Para determinar a que dia da semana pertence a data obtida, aninhe a query dentro da função DAYNAME():

SELECT CURDATE( ) AS 'Hoje', DAYNAME(ADDDATE(LAST_DAY(SUBDATE(CURDATE(), INTERVAL 1 MONTH)), 1)) AS 'Primeiro dia do mês';
+------------+----------------------+
| Hoje       | Primeiro dia do mês  |
+------------+----------------------+
| 2015-07-12 | quarta               |
+------------+----------------------+

Como calcular a idade de uma pessoa no MySQL

Você tem uma ou várias datas de nascimento armazenadas em um banco de dados MySQL e gostaria de calcular as idades de cada pessoa, em relação à data de hoje.
O problema pode ser solucionado computando o intervalo entre as datas — sendo que a mesma abordagem pode ser usada para resolver inúmeras outras situações.
mysql logo over gray ubuntu wallpaper
Determinar a idade envolve calcular o intervalo, decorrido em anos, entre duas datas — mas você não pode fazer isto apenas dividindo a quantidade de dias decorridos por 365, sob pena de obter um resultado inexato — uma vez que este método não inclui os anos bissextos.
Algumas metodologias fazem a divisão por 365,25, o que ajuda a obter resultados menos inexatos.
Neste texto, vou abordar alguns métodos mais precisos para calcular a idade em anos e meses.

Como determinar uma idade em anos

Como já foi dito, não dá para efetuar a operação, contando o número de dias percorridos entre a data de nascimento e a data alvo, uma vez que anos diferentes podem ter uma quantidade diferente de dias.
Uma outra possibilidade é deduzir o ano de nascimento do ano atual. Veja:

SELECT YEAR('2015-06-22')-YEAR('1913-10-19')
    ->  AS "Idade de Vinícius de Moraes"\G
*************************** 1. row ***************************
Idade de Vinícius de Moraes: 102
1 row in set (0.00 sec)

O problema desta abordagem, é que em Junho de 2015, o poeta Vinícius de Moraes ainda teria 101 anos — uma vez que seu mês de nascimento é Outubro.
Ao lidar com clientes, sabemos que algumas pessoas se incomodam quando lhes atribuímos uma idade acima da que realmente têm — por isto é que precisamos determinar suas idades de maneira mais precisa.
Veja outro exemplo (mais complexo) da aplicação deste método.

SELECT NomeCliente AS Cliente,
    ->  DATE_FORMAT(DataNascimentoCliente,'%d de %M de %Y') AS "Data nasc.",
    ->  YEAR(CURDATE()) - YEAR(DataNascimentoCliente) AS Idade
    ->  FROM CadastroClientes
    ->  WHERE YEAR(DataNascimentoCliente) > 1983;

Em destaque, as idades que estão “adiantadas” em relação 22 de Junho de 2015:

+-------------------+-------------------------+-------+
| Cliente           | Data nasc.              | Idade |
+-------------------+-------------------------+-------+
| Christen Wiggins  | 11 de abril de 1984     |    31 |
| Justine Howard    | 22 de dezembro de 1984  |    31 |
| Anika Hammond     | 01 de agosto de 1985    |    30 |
| Alea Vang         | 09 de julho de 1984     |    31 |
| Fredericka Jensen | 29 de fevereiro de 1984 |    31 |
| Alexa Duncan      | 09 de abril de 1985     |    30 |
| Lynn Mclean       | 11 de novembro de 1984  |    31 |
| Allegra Cantrell  | 19 de março de 1984     |    31 |
+-------------------+-------------------------+-------+
8 rows in set (0.00 sec)

Exemplos de uso da função TIMESTAMPDIFF, para calcular a idade exata

Para evitar qualquer desconforto, criado pela falta de exatidão, use a função TIMESTAMPDIFF() — com ela, é possível obter a idade exata de uma pessoa.
Veja um exemplo de uso:

SELECT TIMESTAMPDIFF (YEAR,'1913-10-19',CURDATE())
    ->  AS "Idade de Vinícius de Moraes";

Se a data atual for 22 de Junho de 2015, o resultado para a idade de Vinícius de Moraes é:

+------------------------------+
| Idade de Vinícius de Moraes  |
+------------------------------+
|                          101 |
+------------------------------+
1 row in set (0.00 sec)

A lista de aniversariantes, anterior, também muda (para melhor) com esta abordagem:

SELECT NomeCliente AS Nome,
    ->  DATE_FORMAT(DataNascimentoCliente, '%d de %M de %Y') AS Nascimento,
    ->  TIMESTAMPDIFF(YEAR,DataNascimentoCliente,CURDATE()) AS Idade
    ->  FROM CadastroClientes
    ->  WHERE YEAR(DataNascimentoCliente) > 1983;
+-------------------+-------------------------+-------+
| Nome              | Nascimento              | Idade |
+-------------------+-------------------------+-------+
| Christen Wiggins  | 11 de abril de 1984     |    31 |
| Justine Howard    | 22 de dezembro de 1984  |    30 |
| Anika Hammond     | 01 de agosto de 1985    |    29 |
| Alea Vang         | 09 de julho de 1984     |    30 |
| Fredericka Jensen | 29 de fevereiro de 1984 |    31 |
| Alexa Duncan      | 09 de abril de 1985     |    30 |
| Lynn Mclean       | 11 de novembro de 1984  |    30 |
| Allegra Cantrell  | 19 de março de 1984     |    31 |
+-------------------+-------------------------+-------+
8 rows in set (0.00 sec)

Como obter a idade em meses ou dias

A função TIMESTAMPDIFF() permite estabelecer o resultado em MESES ou DIAS, de maneira simples.
Vejamos quantos meses de idade a cantora Pitty tem:

SELECT TIMESTAMPDIFF (MONTH,'1977-10-07',CURDATE()) AS "Idade da Pitty"\G
*************************** 1. row ***************************
Idade da Pitty: 452
1 row in set (0.00 sec)

Ou em dias:

SELECT TIMESTAMPDIFF (DAY,'1977-10-07',CURDATE()) AS "Idade da Pitty"\G
*************************** 1. row ***************************
Idade da Pitty: 13772
1 row in set (0.00 sec)

Este método pode ser usado em várias outras situações, como estabelecer há quanto tempo um documento está vencido, calcular dívidas etc.
Referência: http://dev.mysql.com/doc/refman/5.7/en/date-calculations.html

Como converter strings de data para padrões aceitáveis pelo MySQL

Uma string, representando uma informação de data, não pode ser gravada corretamente no MySQL, se não estiver de acordo com as normas descritas na ISO 8601.
Para evitar erros e imprevistos, devemos usar algumas funções do MySQL, para tratar as strings e garantir que as datas sejam armazenadas adequadamente, de acordo com o você previa.
mysql-concat-date
Os formatos de data e hora, no MySQL, são expostos em maiores detalhes neste artigo.
Uma dos métodos para converter strings em datas padrão do MySQL, faz uso da função CONCAT().
No exemplo, abaixo, veja que as duas colunas expressam datas:

  • a primeira expressa datas dentro do formato DATETIME e está de acordo com o padrão ISO 8601;
  • a segunda contém datas constituídas a partir da primeira coluna, só que algumas destas datas estão fora do padrão ISO, que pede que o número referente ao mês ou ao dia seja representado por 2 dígitos — ou seja, o correto é ’04’. O mês ‘4’ (sem o dígito zero, à sua esquerda) está fora do padrão e, portanto, invalida esta string, como data passível de ser armazenada.
SELECT DataNascimentoCliente, CONCAT(YEAR(DataNascimentoCliente),'-',MONTH(DataNascimentoCliente),'-',DAY(DataNascimentoCliente)) FROM CadastroClientes LIMIT 5;
+-----------------------+-----------------------------------------------------------------------------------------------------+
| DataNascimentoCliente | CONCAT(YEAR(DataNascimentoCliente),'-',MONTH(DataNascimentoCliente),'-',DAY(DataNascimentoCliente)) |
+-----------------------+-----------------------------------------------------------------------------------------------------+
| 1984-04-11 19:32:05   | 1984-4-11                                                                                           |
| 1984-12-22 02:53:07   | 1984-12-22                                                                                          |
| 1981-05-14 03:12:50   | 1981-5-14                                                                                           |
| 1983-10-01 22:40:44   | 1983-10-1                                                                                           |
| 1969-12-16 11:01:39   | 1969-12-16                                                                                          |
+-----------------------+-----------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

Se temos 3 datas inválidas, na relação acima, vejamos como resolver o problema e inseri-las no padrão de armazenamento de datas do MySQL.
Use a função LPAD() para completar os caracteres faltantes na parte do Mês e na parte do dia:

SELECT DataNascimentoCliente, CONCAT(YEAR(DataNascimentoCliente),'-',LPAD(MONTH(DataNascimentoCliente),2,'0'),'-',LPAD(DAY(DataNascimentoCliente),2,'0')) FROM CadastroClientes LIMIT 5;
+-----------------------+-----------------------------------------------------------------------------------------------------------------------------+
| DataNascimentoCliente | CONCAT(YEAR(DataNascimentoCliente),'-',LPAD(MONTH(DataNascimentoCliente),2,'0'),'-',LPAD(DAY(DataNascimentoCliente),2,'0')) |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------------+
| 1984-04-11 19:32:05   | 1984-04-11                                                                                                                  |
| 1984-12-22 02:53:07   | 1984-12-22                                                                                                                  |
| 1981-05-14 03:12:50   | 1981-05-14                                                                                                                  |
| 1983-10-01 22:40:44   | 1983-10-01                                                                                                                  |
| 1969-12-16 11:01:39   | 1969-12-16                                                                                                                  |
+-----------------------+-----------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

Isto é o suficiente para padronizar as datas da segunda coluna, de acordo com o que o MySQL exige.
O próximo passo é desnecessário — mas tem o objetivo de melhorar a estética do relatório, com a redução da extensão dos títulos das colunas:

SELECT DataNascimentoCliente AS "Data original", CONCAT(YEAR(DataNascimentoCliente),'-',LPAD(MONTH(DataNascimentoCliente),2,'0'),'-',LPAD(DAY(DataNascimentoCliente),2,'0')) AS "Data" FROM CadastroClientes LIMIT 5;
+---------------------+------------+
| Data original       | Data       |
+---------------------+------------+
| 1984-04-11 19:32:05 | 1984-04-11 |
| 1984-12-22 02:53:07 | 1984-12-22 |
| 1981-05-14 03:12:50 | 1981-05-14 |
| 1983-10-01 22:40:44 | 1983-10-01 |
| 1969-12-16 11:01:39 | 1969-12-16 |
+---------------------+------------+
5 rows in set (0.00 sec)

Na forma em que se encontram, agora, os dados da segunda coluna, podem ser armazenados diretamente nesta ou em outra tabela e podem participar de operações aritméticas envolvendo datas.
A função LPAD() serve para ajustar as strings. Possui 3 parâmetros e sua sintaxe é LPAD(str,len,padstr) em que:

  • str — é a string sobre a qual será feita a operação
  • len — o limite de tamanho que a string deve ter
  • padstr — caractere que deverá preencher a nova os espaços “sobrantes”, se houver

O “L” do nome LPAD, quer dizer left (esquerda) e indica de que ponto da string a operação deverá começar.
Por fim, das inúmeras maneiras existentes para se padronizar datas no MySQL para o formato ISO, esta é apenas uma.

Como calcular intervalos de tempo decorrido entre dois horários no MySQL

Se você tem dois valores temporais, um horário de saída e outro de chegada, e deseja saber qual foi o tempo decorrido entre os dois, use a função TIME_TO_SEC() e sua contraparte SEC_TO_TIME().
Ambas, presentes no MySQL (desde a versão 3.22), oferecem uma forma (entre outras) de fazer cálculos envolvendo valores temporais, como horários e datas inteiras.
Você pode ver outros exemplos de uso destas funções aqui.
Se você quer adicionar 3 horas a um horário 15:35:00, não é possível fazer isto apenas somando 15:35:00 + 3 — são unidades diferentes, afinal.

Captura de tela: MySQL calculos envolvendo horas no banco de dados.
Clique para ampliar.

É aí que entra a função TIME_TO_SEC() — ela converte os valores para segundos.
Quando todas as grandezas envolvidas na operação estão usando a mesma unidade, é possível realizar qualquer conta.

SET @HoraSaida='15:20:00';
SET @HoraChegada='17:20:00';
SELECT @HoraSaida, @HoraChegada, TIME_TO_SEC(@HoraChegada) - TIME_TO_SEC(@HoraSaida) AS "Tempo decorrido em segundos";
_
+------------+--------------+-----------------------------+
| @HoraSaida | @HoraChegada | Tempo decorrido em segundos |
+------------+--------------+-----------------------------+
| 15:20:00   | 17:20:00     |                        7200 |
+------------+--------------+-----------------------------+
1 row in set (0.00 sec)

Ao terminar de realizar as operações aritméticas, você converte de volta, usando SEC_TO_TIME() — e voilá (desculpe o clichê) você tem um resultado apresentável, no formato de horário.

SELECT @HoraSaida, @HoraChegada,  SEC_TO_TIME(TIME_TO_SEC(@HoraChegada) - TIME_TO_SEC(@HoraSaida)) AS "Tempo decorrido em horas";
_
+------------+--------------+--------------------------+
| @HoraSaida | @HoraChegada | Tempo decorrido em horas |
+------------+--------------+--------------------------+
| 15:20:00   | 17:20:00     | 02:00:00                 |
+------------+--------------+--------------------------+
1 row in set (0.00 sec)

Note que intervalos de tempo decorrido podem ser negativos — para isto basta que o primeiro valor de horário seja posterior ao segundo. Ou seja, se você saiu de casa às 7:00:00, pro trabalho, e chegou às 6:45:00, você terá gasto -15 minutos de deslocamento — uma verdadeira viagem no tempo.