CODEWITHSUNDEEP
mysqltime-format
Adam Bateman
Adam Bateman

Reputation: 13

SQL How to convert Milliseconds from a table to HH:MM:SS in SQL Query?

PHP version: 7.3.6 using phpMyAdmin

I have seen a few posts about this but I've struggled to find an answer that works for me. I am trying to get the race result time to display in HH:MM:SS which is currently in milliseconds in the table 

SELECT
MIN(Result.Time) AS MinTime, event_cat_id

FROM
Wp_wpa_result Result
INNER JOIN Wp_wpa_event Race ON Result.Event_id = Race.Id 
WHERE Race.Sub_type_id = 'R' AND Age_grade <> 'null' AND event_cat_id IN ( '30', '11', '12', '13', '14', '17', '20', '19', '15'  )
GROUP BY event_cat_id
)


SELECT  U.Display_name, EVC.Name AS EventDistance, EV.name AS RaceName, MinTime,  EV.Date, RE.Age_Grade AS AgeGrade, EV.Sub_type_id AS Terrain, EV.Date AS RaceDate
FROM CTE

INNER JOIN Wp_wpa_event_cat EVC ON EVC.id = CTE.event_cat_id  
INNER JOIN Wp_wpa_event EV ON EV.Event_cat_id = CTE.event_cat_id 
INNER JOIN Wp_wpa_result RE ON EV.id = RE.event_id AND CTE.MinTime = RE.Time
INNER JOIN wp_users U ON U.ID = RE.user_id
ORDER BY MinTime;

Display_name || EventDistance || RaceName || MinTime || Date || AgeGrade || Terrain || RaceDate

Dave Blogs || 5k Worden Place 5k || 1042000 || 06/04/2019 || 74.76 || R || 06/04/2019

But should be

Dave Blogs || 5k Worden Place 5k || 00:17:14 || 06/04/2019 || 74.76 || R || 06/04/2019

Thanks, Adam

Upvotes: 1

Views: 3091

Answers (1)

forpas
forpas

Reputation: 164089

You need the function sec_to_time():

sec_to_time(MinTime / 1000)

If you want the result formatted as hh:mm:ss then use also time_format():

time_format(sec_to_time(MinTime / 1000), '%H %i %s')

Upvotes: 2

Related Questions