How can I group sql rows by hours?

Question

My table:

tbl_questions =>
qid
question
detail
mcid
cid
uid
answercount
dateposted
status
showname
emailnotify
question_type_id

I would like to get all rows group by hours. {dateposted = Y-m-d H:i:s} I would like to update {question_type_id to 2} where I have more rows in that hour.

Well, if someone hase got 15 question per hour I would like to give them type 2, but only those messages (rows) that in that hour. I'm sorry, its hard to tell what I exactly want, my english is poor.

sample data tbl_questions =>

qid = 1, uid = 1, dateposted = 2013-01-01 10:11:10, question_type_id = 1
qid = 2, uid = 1, dateposted = 2013-01-01 10:16:10, question_type_id = 1
qid = 3, uid = 1, dateposted = 2013-01-01 10:18:10, question_type_id = 1
qid = 4, uid = 1, dateposted = 2013-01-01 10:19:10, question_type_id = 1
qid = 5, uid = 1, dateposted = 2013-01-01 10:20:10, question_type_id = 1
qid = 6, uid = 1, dateposted = 2013-01-01 10:22:10, question_type_id = 1
qid = 7, uid = 1, dateposted = 2013-01-01 10:23:10, question_type_id = 1
qid = 8, uid = 1, dateposted = 2013-01-01 10:24:10, question_type_id = 1
qid = 9, uid = 1, dateposted = 2013-01-01 10:25:10, question_type_id = 1
qid = 10, uid = 1, dateposted = 2013-01-01 10:26:10, question_type_id = 1
qid = 11, uid = 1, dateposted = 2013-01-01 10:27:10, question_type_id = 1
qid = 12, uid = 2, dateposted = 2013-01-01 10:17:10, question_type_id = 1
qid = 13, uid = 2, dateposted = 2013-01-01 10:27:10, question_type_id = 1
qid = 14, uid = 1, dateposted = 2013-01-01 12:27:10, question_type_id = 1

I would like to update those rows they posted by uid = 1, Cos that's more than limit in an hour. I need to set question_type_id = 2 in those rows.

So the expected output is: tbl_questions =>

qid = 1, uid = 1, dateposted = 2013-01-01 10:11:10, question_type_id = 2
qid = 2, uid = 1, dateposted = 2013-01-01 10:16:10, question_type_id = 2
qid = 3, uid = 1, dateposted = 2013-01-01 10:18:10, question_type_id = 2
qid = 4, uid = 1, dateposted = 2013-01-01 10:19:10, question_type_id = 2
qid = 5, uid = 1, dateposted = 2013-01-01 10:20:10, question_type_id = 2
qid = 6, uid = 1, dateposted = 2013-01-01 10:22:10, question_type_id = 2
qid = 7, uid = 1, dateposted = 2013-01-01 10:23:10, question_type_id = 2
qid = 8, uid = 1, dateposted = 2013-01-01 10:24:10, question_type_id = 2
qid = 9, uid = 1, dateposted = 2013-01-01 10:25:10, question_type_id = 2
qid = 10, uid = 1, dateposted = 2013-01-01 10:26:10, question_type_id = 2
qid = 11, uid = 1, dateposted = 2013-01-01 10:27:10, question_type_id = 2
qid = 12, uid = 2, dateposted = 2013-01-01 10:17:10, question_type_id = 1
qid = 13, uid = 2, dateposted = 2013-01-01 10:27:10, question_type_id = 1
qid = 14, uid = 1, dateposted = 2013-01-01 12:27:10, question_type_id = 1

http://stackoverflow.com/q/14085923/901048 and http://stackoverflow.com/q/1080207/901048 — Blazemonger, May 23 '13 at 16:27
Check out the questions @Blazemonger attached. The idea is to set with php the dates range and using `between` in the SQL sentence. — Ofir Baruch, May 23 '13 at 16:34
Please provide sufficient *sample* data and the expected result (based on this data). — PM 77-1, May 23 '13 at 16:36
@OfirBaruch: I'm on it. It's a bit difficult, but I try to understood. — user2301881, May 23 '13 at 16:45
Your description specified "*15 questions per hour*". Your results seems to be based on a lesser number. So what is the exact criterion? — PM 77-1, May 23 '13 at 16:50
The question number is a variable.. :-) But now I think 10 per hour is enought. If user hase got more than 10 questions that type is 2. But I have about 1.5 million rows for this. — user2301881, May 23 '13 at 16:53

score 1 · Answer 1 · answered May 23 '13 at 16:56

You will have to proceed in several steps.

Assuming you use MySQL, check the date & time functions. The function HOUR() may be what you want :

SELECT count(id) AS cpt, HOUR(dateposted) AS hour FROM tbl_questions GROUP BY hour ORDER BY cpt DESC;

From here you can extract the maximum count and the associated hours (at least one, maybe several, then retrieve the relevant rows in your table. You may build an array of needed hours and query something like

UPDATE tbl_questions SET question_type_id = 2 WHERE HOUR(dateposted) IN (...your list of hours)

How can I group sql rows by hours?

1 Answers1