CODEWITHSUNDEEP
mysqldatabasetime
user1854392
user1854392

Reputation: 91

How to format time to '2:34 hrs', '0:34 hrs' etc in MySQL

I am writing a query that is selecting two times and calculating the difference between these, where one is the start and the other is the finish to find out the duration. However both are set to TIME types in mysql. How can i format the time instead of showing HH:MM:SS to '[OPTIONAL H]H':MM hrs'

Do i use the extract function? I presume i have to wrap the TIMEDIFF around something else?

my sql code is as follows:

SELECT operator_no, log_in_time, TIMEDIFF(log_in_time,log_out_time) AS Duration

many thanks

Upvotes: 0

Views: 74

Answers (2)

user1854392
user1854392

Reputation: 91

would this not work too?

SELECT operator_no, log_in_time, CONCAT(EXTRACT(HOUR_MINUTE FROM TIMEDIFF (log_in_time,end_time)) + 'hrs' ) AS Duration

Upvotes: 0

PinnyM
PinnyM

Reputation: 35531

Yes, wrap it in TIME_FORMAT():

SELECT operator_no, log_in_time,
       TIME_FORMAT(
           TIMEDIFF(log_in_time, log_out_time),
           '%H:%i hrs'
       ) AS Duration

Upvotes: 2

Related Questions