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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- Cria a tabela de teste DROP TABLE IF EXISTS `teste`; CREATE TABLE `teste` ( `descricao` VARCHAR(60) COLLATE utf8_unicode_ci NOT NULL, `hora_int` INT(11) NOT NULL, `hora` TIME DEFAULT NULL ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; -- Insere os dados INSERT INTO `teste`(descricao, hora_int) VALUES('Teste 09:30', 930), ('Teste 13:42', 1342), ('Teste 21:53', 2153); -- Exibe os dados SELECT * FROM `teste`; |
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:
1 2 3 4 5 6 7 |
SELECT descricao, hora_int, CONVERT(hora_int * 100, TIME) AS time1, SEC_TO_TIME((LEFT(hora_int, CHAR_LENGTH(hora_int) - 2) * 3600) + (RIGHT(hora_int, 2) * 60)) AS time2 FROM `teste` |
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.
Dirceu usei seu script e para alguns valores ele estĂ¡ convertendo errado, por exemplo 275 minutos e estĂ¡ convertendo para 03:15:00
Pode me ajudar ?