CODEWITHSUNDEEP
mysqlsqltime
Chintan7027
Chintan7027

Reputation: 7605

MYSQL Calculate date difference in digital time format

In one of the table I have 2 columns name date1 and date2 having datetime data type

I am calculating difference between these two dates using timediff(date2,date1). Now suppose

date1=2018-04-05 13:10:00 date2=2018-04-05 14:40:00

then the difference between these two dates will be 01:30:00

MY MAIN QUESTION IS how to convert this H:i:s time to digital time format like 01:30:00=1.5 or 01:45:00=1.75?

Upvotes: 0

Views: 133

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

Use time_to_sec to convert to seconds. Then divide by 3600 (60 seconds per minute; 60 minutes per hour) to get to hours:

select time_to_sec(timediff(timestamp '2018-04-05 14:40:00', 
                            timestamp '2018-04-05 13:10:00')) / 60 / 60;

By the way, you can also use timestampdiff instead of timediff to get seconds right away: 

select timestampdiff(second, timestamp '2018-04-05 13:10:00',
                             timestamp '2018-04-05 14:40:00') / 3600;

Upvotes: 2

Janek Wiśniewski
Janek Wiśniewski

Reputation: 21

select hour(timediff(date1, date2)) + minute(timediff(date1, date2))/60 + second(timediff(date1, date2))/60

Upvotes: 0

Related Questions