4
Dear friends, I have a table with a TIME column, which stores the time worked on the day, and I am trying to sum up these times at the end of a month, but without success.
I used the following query:
Query 1:
SELECT TIME_FORMAT((SELECT sum(TOTAL)
FROM horario where PRESTADOR='NOME'
and MONTH(data) = 1 and YEAR(data) = 2015), '%H:%i:%s') as total;
Result "null";
Query 2:
SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(TOTAL))) AS total_horas
FROM horario where PRESTADOR='NOME' and MONTH(data) = 1 and YEAR(data) = 2015;
Result "6.14:24:00";
Query 3:
SELECT sum(TOTAL)
FROM horario where PRESTADOR='NOME'
and MONTH(data) = 1 and YEAR(data) = 2015;
Result: "1526400"; The correct result would be 158:24:00
What exactly are you storing in the database? You can show a snippet of the data from your database?
– gmsantos
Hello, I just solved the problem, but in the bank are stored hours as example "08:48:25". I used time_format to solve the problem, leaving the query :"SELECT time_format( SEC_TO_TIME( SUM( TIME_TO_SEC( TOTAL ) ),'%H:%i:%s') AS total_horas FROM horario Where PROVIDER='NAME' and MONTH(date) = 1 and YEAR(date) = 2015;"
– Thiago
Then don’t forget to answer your own question and mark it as right.
– Jorge B.
But do you store one-time or one-time break per day? For if you are going to do the sum of seconds you will reach a number other than the total worked.
– gmsantos