CODEWITHSUNDEEP
mysql
user222427
user222427

Reputation:

mysql - select hour in datetime and group

I have a datetime column of ShoppingDates.

Lets say I have 1000 rows of

  • 7/18/2012 5:33:39 PM
  • 7/16/2012 6:64:39 PM
  • 7/14/2012 7:34:39 PM
  • 7/13/2012 8:30:39 PM
  • 7/13/2012 8:37:39 PM
    • ect....

    I want to count the number of times that each time appears within an hour time frame

    so 5:00 p.m - 5:59:59 pm
so 6:00 p.m - 6:59:59 pm
so 7:00 p.m - 7:59:59 pm
so 8:00 p.m - 8:59:59 pm

    so in the above result I would get

    1|5pm-6pm
1|6pm-7pm
1|7pm-8pm
2|8pm-9pm

    Any help is appreciated.

    Thanks!

    Upvotes: 27

    Views: 72891

    Answers (2)

    Akash
    Akash

    Reputation: 5012

    Try this

    SELECT 
  CONCAT( HOUR(ShoppingDate), ' to ', CONCAT( HOUR(ShoppingDate), ':59:59' ) ) as time_frame,
  COUNT(*) 
FROM 
  temp
GROUP BY 
  DATE(ShoppingDate), 
  HOUR(ShoppingDate)

    Here's the SQLfiddle

    Note: I assume ShoppingDate is a datetime field

    Upvotes: 4

    Jon Skeet
    Jon Skeet

    Reputation: 1499770

    I suspect you just want something like:

    SELECT HOUR(ShoppingDate), COUNT(*) FROM YourTable
GROUP BY HOUR(ShoppingDate)

    Upvotes: 68

    Related Questions