Generate graphic reports with time

[saet]

Hi Eugenio,

Is it possible to generate a report with time data?

Right now I got what I wanted just by converting the time to seconds, but I would like to be able to do the same thing with the h:mm format or similar.

Thanks,

You are using DaDaBIK version 11.0-Elba enterprise, installed on 07/12/2021 (installation code: 1748061af6f66edae5), the latest version of DaDaBIK is 11.2-Elba released on 16/11/2021
System info
PHP Version: 7.2.24-0ubuntu0.18.04.10
mysql version: 5.7.36-0ubuntu0.18.04.1
Web server: Apache/2.4.29 (Ubuntu)
Client: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:95.0) Gecko/20100101 Firefox/95.0

 

[eugenio]

Hello,
if you have time data in your database it is certainly possible, can you be more specific, what do you want to achieve exactly?

Best,

 

[saet]

Basically I have two tables, one to manage customer repairs, and one to manage internal repairs for my company.
What I want to get is a pie chart, which tells me the total working hours of an operator, divided by customer repairs and internal repairs. I hope I explained myself.

As I told you if I run the query with the total seconds I get the graph, but if I try to format it as "h:mm" then I can't get the graph.

To manage formatting I use this method:

TIME_FORMAT(SEC_TO_TIME( *here I insert the total seconds* ), "%H:%i")

 

[eugenio]

I don't have enough information to answer ... I don't know if you are using a view or an advanced graph, I don't know the whole query you are using, I don't know if you are getting an error message (you just said "I can't get the graph.").

Best,

 

[saet]

Hi Eugenio, I wish you a happy new year.

Sorry if I haven't given you all the information you need to be able to answer me.

- I am extracting data from a normal table.
- I am using the SQL advanced report.
- I am not receiving any error messages, I simply cannot see the chart, but only the labels.

The query I'm running is this:

SELECT "Riparazioni" as "Etichetta", TIME_FORMAT(SEC_TO_TIME(SUM((CAST(SUBSTRING_INDEX (tempoLavoro_riparazioni, ':', -1) as UNSIGNED) + (CAST(SUBSTRING_INDEX (tempoLavoro_riparazioni, ':', 1) as UNSIGNED) * 60))) * 60), "%H:%i") as "Tempo di Lavoro"
FROM riparazioni
WHERE id_operatore = 13 && tempoLavoro_riparazioni IS NOT NULL && YEAR(dataRiparazione_riparazioni) = 2021

UNION

SELECT "Riparazioni Interne" as "Etichetta", TIME_FORMAT(SEC_TO_TIME(SUM((CAST(SUBSTRING_INDEX (tempoLavoro_ripInterne, ':', -1) as UNSIGNED) + (CAST(SUBSTRING_INDEX (tempoLavoro_ripInterne, ':', 1) as UNSIGNED) * 60))) * 60), "%H:%i") as "Tempo di Lavoro"
FROM riparazionisaetinterne
WHERE id_operatore = 13 && tempoLavoro_ripInterne IS NOT NULL && YEAR(dataRiparazione_ripInterne) = 2021

But this is what I get:

https://postimg.cc/MfW1fZC6

And if I use this query, without formatting the total seconds:

SELECT "Riparazioni" as "Etichetta", SUM((CAST(SUBSTRING_INDEX (tempoLavoro_riparazioni, ':', -1) as UNSIGNED) + (CAST(SUBSTRING_INDEX (tempoLavoro_riparazioni, ':', 1) as UNSIGNED) * 60))) * 60 as "Tempo di Lavoro"
FROM riparazioni
WHERE id_operatore = 13 && tempoLavoro_riparazioni IS NOT NULL && YEAR(dataRiparazione_riparazioni) = 2021

UNION

SELECT "Riparazioni Interne" as "Etichetta",SUM((CAST(SUBSTRING_INDEX (tempoLavoro_ripInterne, ':', -1) as UNSIGNED) + (CAST(SUBSTRING_INDEX (tempoLavoro_ripInterne, ':', 1) as UNSIGNED) * 60))) * 60 as "Tempo di Lavoro"
FROM riparazionisaetinterne
WHERE id_operatore = 13 && tempoLavoro_ripInterne IS NOT NULL && YEAR(dataRiparazione_ripInterne) = 2021

I get this:

https://postimg.cc/hQg181dq