CODEWITHSUNDEEP
phpsql
user2301881
user2301881

Reputation: 350

How can I group sql rows by hours?

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

Upvotes: 0

Views: 102

Answers (1)

ChristopheBrun
ChristopheBrun

Reputation: 1207

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)

Upvotes: 1

Related Questions