Jose Felipe Blum de Araujo Jose Felipe Blum de Araujo - 29 days ago 15
MySQL Question

MySQL - SUM DateTime?

I need sum datetime values, but I don't know how I can do this.

I've the table:

enter image description here

And my query:

SELECT
SUM(h.dtplay) AS Time
FROM
tblhistory AS h,
tblgame AS g
WHERE
h.idgame = g.id AND
g.description = gameName;


But, when I run that query, my result is:

enter image description here

Why it's incorrect?

EDIT

I change the format from datetime to time in my table:

enter image description here

So, I need sum the time values.

EDIT 2

The correct query:

SELECT
SEC_TO_TIME(SUM(TIME_TO_SEC(h.DtPlay))) AS Time
FROM
tblhistory AS h,
tblgame AS g
WHERE
h.idgame = g.id AND
g.description = gameName;


Thanks for @Newbee Dev and @EhsanT :)

Answer

First: Format your datetime into just time

Date_format(DtPlay, '%h:%i:%s')

then convert it into just a seconds

Time_to_sec(Date_format(DtPlay, '%h:%i:%s')

after converting it you can now sum them

Sum(Time_to_sec(Date_format(DtPlay, '%h:%i:%s'))

finally the result is now ready and just return it's format as time format

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC( DtPlay ) ) ) FROM tblhistory

Sample Result

Comments