SUM() returns dateformat as string-CodePudding

I have a query that returns the sum of all the "total" fields in my database table.

The query:

    public function findHoursTotal($user)
{
    return $this->createQueryBuilder('h')
        ->where('h.user = :user')
        ->andWhere('h.date BETWEEN :start AND :end')
        ->select("SUM(h.total)")
        ->setParameter('user', $user)
        ->setParameter('start', new \DateTime("midnight first day of this month"))
        ->setParameter('end', new \DateTime("Last day of this month"))
        ->getQuery()
        ->getSingleScalarResult();
}

The query works like intended but i can't get the format right.

For example:

1 = 01:24:00 and 2 = 01:00:00

The query will return the sum of this as 12400.

I tried the DATE_FORMAT() but this returns null:

->select("DATE_FORMAT(SUM(h.total), '%H:%:i%s')")

I tried to convert the string to a dateformat in my controller but php thinks the format is in seconds.

Does anyone know how to get the result from the query in H:i:s?

Thanks!

CodePudding user response：

You cannot SUM the time value. But you can convert TIME_TO_SEC, calculate sum of seconds, then convert SEC_TO_TIME:

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(total)))
FROM h
WHERE ...
-- should give you something similar to 02:24:00