The goal: grab number of hours and minutes from a planning stored in a MySQL database, and get it into EXCEL to build indicators in pivot tables and graphs.

Problem: Times per events are stored in MySQL in the form « HH:mm ». Ex: « 25:30 » for 25 hours and 30 minutes.

If your use MS Query to get it in your EXCEL datasheet, il will display fine, but impossible to add hours with « SUM » or SUBTOTAL » functions.

The fact is that EXCEL stores hours and minutes in nnumber of days, with decimal.

So in this example 25 hours and 30 minutes are « 1.0625 » days for EXCEL !!

So we need to convert in our SQL request, as follow:


clients.nom_com AS client_nom_com,
CONCAT(tech.nom,’ ‘,tech.prenom) as technicien,
planning.ca_num as affaire,
tpe.event_time_per_day AS temps_par_journee_evt,
tpe.event_nb_days AS nombre_de_jours_evt,
substring_index(tpe.event_total_time,’:’,1)/24+ substring_index(substring_index(tpe.event_total_time,’:’,2),’:’,-1)/60 AS temps_total_evt
FROM planning
LEFT JOIN thirds AS clients ON planning.client_id=clients.third_id
LEFT JOIN plg_event_types pet ON planning.plg_event_type_id=pet.plg_event_type_id
LEFT JOIN persons as tech ON planning.tech_id=tech.person_id
LEFT JOIN v_total_time_per_event tpe ON planning.plg_event_id=tpe.plg_event_id;

Print Friendly, PDF & Email

Leave a Reply

You have to agree to the comment policy.

Blue Captcha Image


Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.