MySQL – Como converter hora armazenada como inteiro para uma coluna do tipo time

Olá pessoal,
Boa noite!

Nesse post de hoje vou demonstrar como converter hora armazenada como inteiro para uma coluna do tipo time. Tive a ideia de fazer esse post quando um colega DBA me contou que ele está administrando um sistema que utiliza o banco MySQL e tem uma tabela onde o programador resolver armazenar hora em um campo inteiro, de forma que 09:30 seja armazenado com 930 e 21:20 seja armazenado como 2120, por exemplo.

Infelizmente, isso não era novidade pra mim e isso me motivou a escrever esse post, pois pode ajudar a outras pessoas, seja o DBA a conseguir trabalhar com esses dados ou seja o Desenvolvedor, para que ele utilize campo datetime ou time para armazenar esse tipo de informação.

Simulando a base de testes

Para que vocês possam simular esse problema em casa, resolvi disponibilizar esse script simples:

Result:

Convertendo hora armazenada como uma coluna INT para TIME

Depois de fazer essa introdução e postar o script de testes, vou demonstrar agora como realizar essa conversão de inteiro para time.

Utilizando o CONVERT
Uma forma rápida de se fazer isso é utilizando a função CONVERT do MySQL para converter o número para TIME. Se tentarmos realizar a conversão diretamente, o MySQL vai transformar os dados, mas ele vai entender que os dados estão representados na forma mm:ss, conforme print abaixo:

Para que ele entenda que o nosso formato de hora é HH:mm:ss, vou multiplicar o número por 100, de forma que o “minuto” passe a ser hora, o “segundo” passe a ser minuto e os segundos serão zerados.

Caso você armazene os segundos também no campo inteiro, não precisa multiplicar por 100:

Convertendo para segundos e depois utilizando a função SEC_TO_TIME()
Uma outra forma de se fazer isso é:

• Separar manualmente o valor do campo de hora (LEFT(hora_int, CHAR_LENGTH(hora_int) – 2)
• Uma vez obtido o valor da hora, multiplica-se a hora obtida por 3600 (60 x 60), para calcular a quantidade de segundos que compõem a hora
• Separar manualmente o valor do campo de minuto (RIGHT(hora_int, 2))
• Uma vez obtido o valor do minuto, multiplica-se o minuto obtido por 60, para calcular a quantidade de segundos que compõem os minutos
• Soma-se a quantidade de segundos da hora com a quantidade de segundos dos minutos, para calcular a quantidade de segundos total
• Uma vez convertido para segundos, podemos utilizar a função SEC_TO_TIME() do MySQL para obter a hora correspondente da quantidade de segundos calculada

E agora podemos conferir a solução resumida:

Resultado Final:

Utilizei a instrução SELECT para exibir o resultado final, mas vocês podem utilizar com UPDATE tranquilamente, convertendo de INT para TIME. Nesse caso, recomendo criar uma nova coluna do tipo TIME, realizar o UPDATE, e caso esteja tudo certo, apagar a coluna INT.

Espero que tenham gostado do post!
Qualquer dúvida, é só deixar nos comentários.