CODEWITHSUNDEEP
phpmysqlsqltimestampunix-timestamp
user962449
user962449

Reputation: 3873

How do you count the distinct values for each day using a timestamp column?

I'm using mysql and I'm having trouble thinking of a query to count the number of users/visitors for a certain date range. The way that I'm currently doing it is using php, I select the date range and process the data in a for loop and then just count them there. It's actually pretty easy, but the problem is that this method does not work for bigger data of a few million rows. The alternative is to count the distinct values using mysql only and just return a count and not actual data by utilizing the index on the timestamp column. Also, converting the column to a datetime is not an option. Any ideas how I can achieve this?

Here's a sample result set of what I need:

date     | count
5-01-13     14
5-02-13     44
5-03-13     23
5-04-13     13

My problem is that I don't know how to group the timestamp column by day.

Upvotes: 1

Views: 1595

Answers (2)

Chief Wiggum
Chief Wiggum

Reputation: 2934

That should do the trick:

SELECT DATE('datetimecolumn'), COUNT(*) 
  FROM Table 
 GROUP BY DATE('datetimecolumn')

Upvotes: 5

isJustMe
isJustMe

Reputation: 5470

You just have to do the same, but instead add a group by clause:

SELECT myDate, count(distinct myField) as cnt
FROM myTable
WHERE myDate BETWEEN ? and ?
GROUP by myDate;

Where the "?" are the dates you use in your original query.

Upvotes: 0

Related Questions