1

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?

Improve this question

5 Answers 5

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0 
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");
Improve this answer

Comments

0 
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

Improve this answer

Comments

0

From timestamps, not dates:

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

Improve this answer

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.