We are facing an issue to make to sum of each duration (with sql Table type time: HH:MM:SS)
Currently we have a SQL Table with each field, below:
monitoring_system
Table: vantage_jobs
Columns:
id int(11) AI PK
JobName varchar(100)
State varchar(100)
Started datetime
WorkflowName varchar(1000)
JobRunTime type time
JobQueueTime time
VimnBrand varchar(100)
InputFileDuration type time
TranscodingTime type time
JobType varchar(100)
FinalCustomer varchar(100)
FinalPlatform varchar(100)
file_rec_date datetim
Please focus about InputFileDuration type, when we make basic request sql:
SELECT * FROM monitoring_system.vantage_jobs;
We have “InputFileDuration” -> HH:MM:SS for exemple 00:20:10.
This is each file duration.
When we use this request in MySQL:
SELECT WorkflowName AS Workflow_name,count(*) AS NUMBERS, SEC_TO_TIME( SUM( TIME_TO_SEC (InputFileDuration) )) AS InputFileDurations FROM vantage_jobs.
We have that we want, sum of each file duration with SEC_TO_TIME( SUM( TIME_TO_SEC (InputFileDuration) )) AS InputFileDurations .
However, in Metabase, we can’t use SEC_TO_TIME and TIME_TO_SEC.
And we have make a lot of test, with editing sql, but nothing else.
Could you please help me about this ? or if you have the best way to make this sum of duration ?
In advance many thanks
best regards