Skip to content

Instantly share code, notes, and snippets.

@silentsudo
Created June 5, 2020 11:10
Show Gist options
  • Save silentsudo/e4873c17f07c66ff509c0281ba67abb5 to your computer and use it in GitHub Desktop.
Save silentsudo/e4873c17f07c66ff509c0281ba67abb5 to your computer and use it in GitHub Desktop.
convert time store in sql to hh:mm
create table total_minutes(
id bigint(20) NOT NULL AUTO_INCREMENT,
total_time int,
primary key(id)
);
insert into total_minutes(total_time) values(35);
insert into total_minutes(total_time) values(95);
insert into total_minutes(total_time) values(69);
insert into total_minutes(total_time) values(392);
insert into total_minutes(total_time) values(4501);
SELECT total_time, floor(total_time/60) as hours, lpad(floor(total_time%60), 2, '0') as minutes,
concat(floor(total_time/60), ":", lpad(floor(total_time%60), 2, '0')) as hh_mm FROM classifiedads.total_minutes;
'35', '0', '35', '0:35'
'95', '1', '35', '1:35'
'69', '1', '09', '1:09'
'392', '6', '32', '6:32'
'4501', '75', '01', '75:01'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment