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:

SELECT
plg_event_debut,
plg_event_fin,
pet.plg_event_type_code,
pet.plg_event_type_descr,
plg_event_descr,
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;