CODEWITHSUNDEEP
mysql
Aaron Liu
Aaron Liu

Reputation: 149

MySQL group by TimeStamp

I am writing a Python code where I need to use MySQLdb to retrieve data from a MySQL database. A part of the original database looks like this:

I used this command 

SELECT TimeStamp,Pac 
FROM SolarData 
WHERE DATE(`TimeStamp`) = CURDATE() 
GROUP BY HOUR(TimeStamp);

to group the data by hour, but the result is not what i expected:

The Pac number shown for every hour is the same number as the first record of each hour. It's not an accumulated number for the whole hour. What I need is an accumulated number of the whole hour.

Upvotes: 2

Views: 454

Answers (2)

Jason Heo
Jason Heo

Reputation: 10236

"How do I decorate the code so that the hour format can have the date on it as well like this 2014-01-14 07:00"

All of Hour belong to today (CURDATE()), and second part is always ':00', so following query might help you. Could try this?

SELECT CONCAT(CURDATE(), ' ', Hour, ':00'), Pac
FROM (
    SELECT HOUR(TimeStamp) AS Hour, 
        SUM(Pac) AS Pac
    FROM SolarData 
    WHERE DATE(`TimeStamp`) = CURDATE() 
    GROUP BY HOUR(TimeStamp)
) x;

Upvotes: 1

Kermit
Kermit

Reputation: 34054

That's because MySQL is like your alcoholic uncle when you don't use GROUP BY by the ANSI standard. You probably want:

SELECT HOUR(TimeStamp) AS Hour, 
    SUM(Pac) AS Pac
FROM SolarData 
WHERE `TimeStamp` >= CURDATE() 
  AND `TimeStamp` < CURDATE() + INTERVAL 1 DAY
GROUP BY HOUR(TimeStamp);

It would be helpful to see the desired result you're looking for. Until then, the above query is just a guess based on group the data by hour. For future reference, use SQL Fiddle to post your table structure/data.

Upvotes: 5

Related Questions