How to select unique visitors per date interval?

Question

I've found a few query strings related to what I'm looking for, but nothing that does this. Basically, I have a graph that needs to display unique visitors per time interval (hour, day, month, or year). So, a graph similar to Google Analytics or this Analytics Graph. The table has a visitor ID column and a timestamp column (in the style 0000-00-00 00:00:00). I need to be able to select the unique or total visitors per interval (eg. 32 visitors on the 5th, 30 on the 6th, etc).

I guess my question is, is there anyway to do this efficiently with just a query? Or will I need to use PHP to get all the data (problem with that is that I would have to do a query for each point on the graph, not efficient)?

Looks pretty much like a duplicate of this question: http://stackoverflow.com/questions/366603/mysql-sql-group-by-date-only-on-a-datetime-column — jefflunt, Jun 30 '11 at 02:13

ariefbayu · Accepted Answer · 2011-06-30T07:29:38.297

You will need to aggregate your data. Try this query:

SELECT DATE(data_timestamp), COUNT(visitor_id)
FROM analytics_table
WHERE DATE(data_timestamp) BETWEEN '2011-05-01' AND '2011-05-31'
GROUP BY 1

Here's how you populate your monthly data (WARNING: UNTESTED!):

<?php
$sql = "SELECT DATE(data_timestamp), COUNT(visitor_id)
FROM analytics_table
WHERE DATE(data_timestamp) BETWEEN '2011-05-01' AND '2011-05-31'
GROUP BY 1";

$rs = mysql_query($rs);
$date1 = $datex = '2011-05-01';
$date2 = '2011-05-31';

$arrayData = $tmpArray = array();

while( $r = mysql_fetch_array($rs) )
{
   $tmpArray[$r['date']] = $r['count'];
}

while( $datex <= $date2)
{
   if( isset($tmpArray[$datex]) )
   {
      $arrayData[$datex] = $tmpArray[$datex];
   }
   else
   {
      $arrayData[$datex] = 0;
   }
   list( $y, $m, $d) = explode('-', $datex);
   $datex = date('Y-m-d', mktime(0, 0, 0, $m, $d, $y));
}


?>

this query use:

DATE() to get the date from your data and
COUNT() to count total data from that particular date.
and GROUP BY to group your data based on what field you select.

That's a simple version of what I had tried, but that doesn't solve the problem of getting the values for each day (or month or year). For example, the graph should show 1 month of activity with the number of unique users per day. — Coolist, Jun 30 '11 at 03:01
Okay, thanks for your help. We ended up using: SELECT COUNT(DISTINCT user_id), DATE(time) FROM tracking_pageview WHERE site_id='$this->site_id' AND DATE(time) > DATE_SUB(NOW(), INTERVAL $days DAY) GROUP BY DATE(time) — Coolist, Jun 30 '11 at 16:56

score 0 · Answer 2 · edited May 23 '17 at 12:27

0

Basically, you can cast the DATETIME/TIMESTAMP into dates, and times separately, and then use a GROUP BY to get the results you need.

MySQL/SQL: Group by date only on a Datetime column

edited May 23 '17 at 12:27

Community

1
1

answered Jun 30 '11 at 02:14

jefflunt

33,527
7
88
126

How does one use that with a date interval? (Eg. total each day for 1 month) – Coolist Jun 30 '11 at 03:02
Cast it to a DATE only (which drops the time - 1/1/01, 1/2/01, 1/3/01, etc.), and in the SQL statement you COUNT(*) and GROUP BY the date alone. – jefflunt Jun 30 '11 at 11:50

score 0 · Answer 3 · answered Jun 30 '11 at 04:43

GROUP BY doesn't return 0 value if there is no visitor some day. That will yield to the holes in time continuity. There are two ways:

1) Group like others said and then find holes

2) Create aggregation table with fields "date", "count" etc. and insert there data every day by cron.

How to select unique visitors per date interval?

3 Answers3

Linked