SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group by time (hourly) within 24 hours

    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?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I already have more than 5000 records in my database set to this already. Any way to test with PHP code?

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You can change it to a DATETIME type if it's not already easily:

    Code:
    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;

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SitePoint Guru
    Join Date
    Jul 2005
    Location
    Orlando
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dinheiro View Post
    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.

  7. #7
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dinheiro View Post
    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.

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

  8. #8
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

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

  9. #9
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That's how many orders were placed at 10PM on any date.

    Restrict it to only today:

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

  10. #10
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm. Still doesn't work well. It's almost 11pm now and it only gives me a count on records up til 7pm.

  11. #11
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dinheiro View Post
    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.

  12. #12
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  13. #13
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  14. #14
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  15. #15
    SitePoint Enthusiast
    Join Date
    May 2005
    Posts
    93
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay. Guess no one has solution to this. Thanks for the help Dan.

  16. #16
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You'll need to store every user's timezone somewhere, and in your database queries, use CONVERT_TZ to apply the offsets.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •