MySQL Select Function

Question

I have three fields in my table called data. The fields are called timestamp, locationID and event. The below mySQL query is intended to take the data from the table and create four columns: timestamp, movement, peoplein and peopleout.

When I run the query, I get the following message: MySQL returned an empty result set (i.e. zero rows). There definitely is data in the table so I'm wondering if someone could help me spot the issue with the code below.

SELECT FROM_UNIXTIME( FLOOR( UNIX_TIMESTAMP( TIMESTAMP ) / 600 ) *600 ) AS `timekey`, 
SUM( event ) AS `movement`, 
SUM( IF( event > 0, event, 0 ) ) AS `peoplein`, 
SUM( IF( event < 0, ABS( event ) , 0 ) ) AS `peopleout`
FROM `data`
WHERE DATE( TIMESTAMP ) = DATE( NOW( ) ) 
GROUP BY `timekey`
ORDER BY `timekey` ASC

Answer 1

WHERE DATE( TIMESTAMP ) = DATE( NOW( ) )

There may well be data in the table but, unless there's data in the table for today, you're not going to see anything.

In terms of debugging SQL generally, it's often a good idea to strip things away until it starts to deliver some results (or start from a minimum and add things until it doesn't). The last thing removed/added will then be the likely culprit and you can narrow your focus to that.

• James McDonnell: For debugging, I would also suggest downloading mysql workbench to test your queries. Or any sql management tool for each different platform.

Answer 2

The expression:

WHERE TIMESTAMP = DATE( NOW( ) ) 

would fail because Timestamp has a time component. Although using date fixes it, it is recommended to do:

where timestamp >= curdate() and
 timestamp < date_add(curdate(), interval 1 day)

This version allows your query to take advantage of an index on timestamp.

• paxdiablo: Gordon, it doesn't fail as you seem to suggest, so you may want to clean up that bit. It may not be as efficient as your proposed query but it definitely works and the use of date() will in no way cause the "empty result set" issue. You can see that if you just enter create table x(y timestamp); insert into x values (now()); select * from x where date(y) = date(now()); into MySQL.
• Gordon Linoff: @paxdiablo . . . That is really strange, I didn't notice the DATE() in the original query (I thought that was the problem). I changed the wording.