Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL - SUM DateTime?

Tags:

datetime

mysql

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 :)

like image 518
Jose Felipe Blum de Araujo Avatar asked Nov 07 '16 02:11

Jose Felipe Blum de Araujo


1 Answers

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

like image 90
Beginner Avatar answered Sep 29 '22 09:09

Beginner