MySQL: Retrieving count of records from a particular time frame

Question

So what I'm trying to do is a bit tricky. I'm trying to come up with a sql query that will be able to return counts for specified time frames. So I have a table 'v'. Here is some basic data from Table v

Table v
_____________
id    p_id    created_at
 1     1      2009-06-09 18:54:17
 2     2      2009-06-09 21:51:24
 3     2      2009-06-10 18:53:51
 4     1      2009-06-10 01:20:36
 5     1      2009-06-10 11:20:36

Basically, I want to get results back for a specified time frame (hour, day, week, month, year). I've got this somewhat working for days...but am unable to return results for timeframs containing a count of zero. Basically I want to give it a time frame and a delimiter(hour, day, etc.) and be able to get the number of rows from table v within that time frame.

This is what I currently tried:

select count(*) as count, date_format(created_at, "%m/%d/%y") as date from v where p_id = 56 group by date_format(created_at, "%m/%d/%y");

Returns

+-------+-------------------------------------+
| count | date_format(created_at, "%m/%d/%y") |
+-------+-------------------------------------+
|     3 | 06/09/09                            | 
+-------+-------------------------------------+

But this doesn't take into consideration a time frame. Any ideas?

Answer 1

The usual thing I would recommend is doing it like:

SELECT COUNT(*) AS `num`
FROM `table`
WHERE `created_at` >= '2009-09-06 00:00:00'
AND `created_at` < '2009-09-07 00:00:00'

i.e. straight querying within your date/time range, which addresses the issue of different scales of timespan and so on. With enough records in the table, an index on created_at can become useless, but it should still do better than comparing against a DATE_FORMAT result; unless the query optimizer is way smarter than I give it credit for, that will run the DATE_FORMAT against every row and compare the result, rather than getting any use out of indexing at all.

I suspect there are things I'm not processing about your situation that make this not work for you, or you'd already be doing it that way, but I suppose I'll have to wait for feedback to see what those are.

Answer 2

SELECT
  COUNT(*) AS cnt,
  DATE_FORMAT(created_at, "%m/%d/%y") AS my_date
FROM
  v
WHERE
  p_id = 56 AND 
  created_at BETWEEN first_date AND second_date
GROUP BY 
  DATE_FORMAT(created_at, "%m/%d/%y");

Answer 3

SELECT COUNT(*), created_at
FROM v
WHERE p_id = 56 
   and created_at >= sometime and created_at <= dateadd(hour,1,sometime)

You can also add days, years, etc to get the time frame you are wanting.

See this link for more details: http://msdn.microsoft.com/en-us/library/ms186819.aspx

Answer 4

From timestamps, not dates:

SELECT COUNT(*), DATE_FORMAT(FROM_UNIXTIME(created_at), "%m.%y" ) AS mDate FROM v GROUP BY mDate

Answer 5

Once you don't have any record for a given time period, there is no way to the MySQL server the sum that period. All the proposed solutions (including yours) work, but won't return any rows for a time frame with no records.

If you are using this query in an application, I suggest you to create the rows with zero count manually on your grid, or whatever you are using to show the results.