Group by time (hourly) within 24 hours

[dinheiro]

I have a database where with each record a 10 digit integer unixtimestamp gets placed in a field called dateTime using the time() in php.

What I would like to do is count how many records were entered on an hourly basis, for example:

7:00pm = 20
6:00pm = 10
5:00pm = 40

my database has the format:

id | fileName | dateTime
------------------------------
1 | Lucky.jpg | 1241215200

Is this possible?

[Dan Grossman]

Make this a MySQL DATETIME column if it isn't already, and GROUP BY HOUR(dateTime)

http://dev.mysql.com/doc/refman/5.1/...#function_hour

If you're just calling time() when you insert these records, then you should use CURRENT_TIMESTAMP in your query instead. MySQL knows what time it is and has column types specifically for storing dates and times.

[dinheiro]

I already have more than 5000 records in my database set to this already. Any way to test with PHP code?

[Dan Grossman]

You can change it to a DATETIME type if it's not already easily:

ALTER TABLE table ADD COLUMN newdate DATETIME;
UPDATE table SET newdate = FROM_UNIXTIME(`dateTime`);
ALTER TABLE table DROP COLUMN `dateTime`;
ALTER TABLE table CHANGE newdate `dateTime` DATETIME;

[dinheiro]

Hmmm. Is there a difference between php unixtimestamp and mysql timestamp? I really don't want to have to change my database or application. In php there is no other way to programatically organize php unixtimestamps hourly within a 24 hour period?

[UFTimmy]

Hmmm. Is there a difference between php unixtimestamp and mysql timestamp? I really don't want to have to change my database or application. In php there is no other way to programatically organize php unixtimestamps hourly within a 24 hour period?

Sure.. you could do it in PHP. But I'm assuming you came here for good advice, which is what Dan is providing.

[Dan Grossman]

Hmmm. Is there a difference between php unixtimestamp and mysql timestamp? I really don't want to have to change my database or application. In php there is no other way to programatically organize php unixtimestamps hourly within a 24 hour period?

You can do it with PHP pretty easily. The problem is you have to retrieve all 5000 rows from the database before you can start grouping them with your code. If you do it in a query, you only have to retrieve 24 rows. It will be *much* faster.

If you really don't want to change the type of the column, you can cast the unix timestamps to a date in the query, using FROM_UNIXTIME as I mentioned before.

SELECT HOUR(FROM_UNIXTIME(`dateTime`)) AS `hour`, 
...
FROM table
GROUP BY HOUR(FROM_UNIXTIME(`dateTime`))

[dinheiro]

This doesn't work the way I want it to. I did a simple count of how many files were uploaded within the last hour and the amount I totaled by hand were far less than what the mysql query totaled for me.

Right now it's 10pm EST and my count for 10pm EST is showing 10, but what I get back from the mysql query total is 175, which is far from correct.

 SELECT COUNT( file ) AS total, HOUR( FROM_UNIXTIME( `dateTime` ) ) AS `hour`
FROM uploads
WHERE `file` != ''
GROUP BY HOUR( FROM_UNIXTIME( `dateTime` ) )

All I really want is a running count on a 24 hour basis of how many files were uploaded like this:

10pm = 10
9pm = 2
8pm = 12
7pm = 22
6pm = 100
5pm = 13
4pm = 23
...
11pm = 12

[Dan Grossman]

That's how many orders were placed at 10PM on any date.

Restrict it to only today:

WHERE `file` != '' 
  AND DATE(FROM_UNIXTIME(`dateTime`)) = CURDATE()

[dinheiro]

Hmm. Still doesn't work well. It's almost 11pm now and it only gives me a count on records up til 7pm.

[Dan Grossman]

Hmm. Still doesn't work well. It's almost 11pm now and it only gives me a count on records up til 7pm.

Run this query:

SELECT CURRENT_TIMESTAMP

Or in your PHP code, echo date('m/d/Y H:i:s')

Your server is probably set to a different timezone than you.

[dinheiro]

Yes, it is set to a different timezone. It's 11pm here and the time on the server is 8pm. What do I need to do to make it work correctly for everyone across all timezones? BTW, thanks for your help and super fast response. I appreciate it totally.

[Dan Grossman]

Do you need the output to be localized to whoever's viewing it, or do you just want to adjust the results by 3 hours?

[dinheiro]

I think it would help greatly to be localized. The reports will be read by people in different timezones. Also, can I do the same thing for a breadkdown of days, weeks, months?

[dinheiro]

Okay. Guess no one has solution to this. Thanks for the help Dan.

[Dan Grossman]

You'll need to store every user's timezone somewhere, and in your database queries, use CONVERT_TZ to apply the offsets.