CODEWITHSUNDEEP
mysql
Val
Val

Reputation: 413

how to group by hour and 30 minutes

I have a list of times in datetime format

datetime action
11:13:40 eat
12:33:40 sleep
14:44:40 walk
14:55:40 eat
14:59:40 call

i want to group by 

11:00am eat
12:30pm eat
2:30pm walk
2:30pm eat
2:30pm call

Upvotes: 0

Views: 173

Answers (2)

spencer7593
spencer7593

Reputation: 108410

Here's one way:

SELECT DATE_FORMAT(`datetime`,IF(MINUTE(`datetime`)<30,'%l:00%p','%l:30%p'))
     , action
  FROM ...
 ORDER BY 1

To unwind that a little bit: we use the MINUTE function to extract the minutes portion from the datetime value. If that is less than 30, then we specify '00' as the minutes portion, otherwise, we specify '30'. The rest is just formatting. %l gives us the hour value from 1 to 12, no zero padding. The %p gets us the 'AM' or 'PM' value. (If you need this in lower case, then you could wrap that entire DATE_FORMAT expression in a LOWER() function.

Upvotes: 1

Val
Val

Reputation: 413

SELECT REPLACE(DATE_FORMAT(datetime,'%h %p'),
               ' ' ,
               IF(CEIL(MINUTE(datetime)/30)*30=60,':30 ',':00 '))  AS hour_time
       , action    
FROM table1
GROUP BY 1

Upvotes: 0

Related Questions