0

I need to group rows from a table by 24 hours intervals as below

// SELECT count(user_id), DATE(created) as day FROM table GROUP BY day //

SELECT * FROM table GROUP BY DATE(created)

but i need the selection to be made by 24 hours intervals starting at 12:00:00 not 00:00:00

Thanks

Marius Marus
  • 7
  • 7
  • Do you only need the date? – Marty McVry Aug 24 '13 at 13:55
  • i need to list the table rows group by 24 hours intervals starting from 12:00:00 – Marius Marus Aug 24 '13 at 14:09
  • What aggregate function do you want to apply? If you want to display all records, you should do the grouping programmatically... – Marty McVry Aug 24 '13 at 14:11
  • SELECT * FROM table GROUP BY DATE(created) is the query i am using right now – Marius Marus Aug 24 '13 at 14:13
  • Using an SQL `GROUP BY` is only useful if you are going to use aggregate functions (SUM, MAX, AVG ...) on data from your table. I'm assuming you just want some sort of new 'header' in your results table every time a new 'group' is reached? This has to be done when you format your output. **OR** Do you want to enter a date and select every row that is created from 12:00:00 that day to 12:00:00 the next day? – Marty McVry Aug 24 '13 at 14:15
  • how can i achieve this? it's a users table and has id username password and created columns. i need all the users in the table, not a starting and ending date – Marius Marus Aug 24 '13 at 14:19
  • Define "this"... Also, what other language are you using? PHP? – Marty McVry Aug 24 '13 at 14:22
  • how can i achieve this "group by select" ? using php, yes. thanks – Marius Marus Aug 24 '13 at 14:31

1 Answers1

0

Use the following SQL-query:

SELECT *, TIMESTAMPADD(HOUR, 12, DATE(TIMESTAMPADD(HOUR, -12, created))) AS grp
FROM table
ORDER BY created DESC

This puts the most recent added users on top. You can change DESC to ASC if you want the oldest users on top.

Also, what I've added is a grp column. It will help you use grouping in PHP. The result from that column is f.e. August, 24 2013 12:00:00+0000. Whenever this value changes, you need to create a new group header in PHP.

Now, I could provide you with some code, but it's dependent on what sort of database-access you're using (mysqli_*-functions or PDO), so I won't go into much detail about that.

What I can provide is a link for you containing more information on grouping records using a while loop, which has been provided as an answer to a different question here on SO.

EDIT

To display only the number of users (and the beginning of the timeframe), you would use a different approach:

SELECT COUNT(id), TIMESTAMPADD(HOUR, 12, DATE(TIMESTAMPADD(HOUR, -12, created))) AS grp
FROM table
GROUP BY grp
ORDER BY created DESC;

To see the query in action, check out this SQL-Fiddle.

Community
  • 1
  • 1
Marty McVry
  • 2,838
  • 1
  • 17
  • 23
  • SELECT COUNT( id ) , DATE( created ) AS DAY FROM users GROUP BY DAY this counts ids of created users each day. but the grouping is starting at 00:00:00 i need the created group by to start at 12:00:00 so i can count users created each 24 hours interval. what i ask makes sense? – Marius Marus Aug 24 '13 at 14:49
  • @MariusMarus Oh, you only want to count. Why didn't you say so? - Check edit! – Marty McVry Aug 24 '13 at 17:53
  • excuse my poor english :) – Marius Marus Aug 24 '13 at 19:22
  • 1 2013-06-25 16:06:53 2013-06-25 12:00:00 6 2013-06-26 11:29:04 2013-06-25 12:00:00 rock solid, i have to dive into TIMESTAMPADD. awsome select, thank you – Marius Marus Aug 24 '13 at 19:42
  • i just want to add this. the table is innodb, adding an index to created column will optimize this query – Marius Marus Aug 24 '13 at 20:11