CODEWITHSUNDEEP
phpmysql
PinoyStackOverflower
PinoyStackOverflower

Reputation: 5302

MySQL datetime datatype get total number of visitors on a specific date range

I have this kind of entries in my database table:

enter image description here

In the Web Application that i'm creating, I am letting users select a custom dates range, so for example, one user would select a date range from 2014-05-13 to 2014-05-15. I want to get the total number of nVisitors for each day using MySQL.

If you noticed that 2014-05-13 date has a time, so I want to get the total of nVistors of per day depending on the date range the user selected on my web app.

An example output would be:

   Date    |   total
2014-05-13 |    146
2014-05-14 |    200
2014-05-15 |    150

Given that the total column was a result of the nVisitors per day that the user has selected.

Any ideas please. Your help will be greatly appreciated! Thanks! :)

Upvotes: 0

Views: 650

Answers (4)

PinoyStackOverflower
PinoyStackOverflower

Reputation: 5302

Thanks for your answer guys, because of you guys, I had an idea how to approach this problem.

This is my code:

$sql = "select DATE(date),sum(nVisitors) from 
  store_visitors where `date` between 
  '$startDate 00:00' and '$endDate 23:59' group by DATE(date)";

I added a 00:00 and a 23:59 at the end of the date range since the data type of my columns are datetime.

Thanks again! :)

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

You may try this:

select date(`date`), sum(total) from yourtable 
where date(`date`) between date('2014-05-13') and date('2014-05-15') 
group by date(`date`)

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 1549

Try Below:

select DATE(date) as Date,count(idStoreVisitor) as Total from yourTable where Date(date) >= <User_inputDateRange1> 
and Date(date)<= <User_inputDateRange2> group by Date(date)

Upvotes: 1

Denis Kohl
Denis Kohl

Reputation: 750

you can use the day interval in your query.

select count(*) from <table> where last_login > (curdate() - interval 30 day) order by <date column> desc;

Upvotes: 0

Related Questions