CODEWITHSUNDEEP
phpmysqlsqllaravellaravel-4
Stromgren
Stromgren

Reputation: 1074

Counting timestamps with Laravel 4 Query Builder

I have a table logging requests to my site with an ip and a timestamp in DateTime format. I run a query to check the ip's with most requests like this.

$ips = DB::table('requests')
   ->groupBy('ip')
   ->select('ip', DB::raw('COUNT(DISTINCT created_at) as days'), DB::raw('COUNT(*) as requests'))
   ->orderBy('requests', 'desc')
   ->take(50)
   ->get();

The "days" attribute is supposed to count the number of days an ip address has been active. My problem is that my timestamps are Y-m-d H:i:s, which means, by counting the DISTINCT timestamps, i get basically the same count as with requests. Hence i need a way to only count the timestamps by day, month and year.

Any help is very much appreciated.

Upvotes: 0

Views: 679

Answers (1)

c 2
c 2

Reputation: 1167

Try using date_format inside COUNT(DISTINCT)

CREATE TABLE mytable (
id INT PRIMARY KEY AUTO_INCREMENT,
created_at DATETIME
)

INSERT INTO mytable (created_at) VALUES ('2013-01-01 12:00:01');
INSERT INTO mytable (created_at) VALUES ('2013-01-01 20:00:01');
INSERT INTO mytable (created_at) VALUES ('2013-02-01 20:00:01');

SELECT COUNT(DISTINCT DATE_FORMAT(created_at, '%Y-%m-%d')) FROM mytable

Upvotes: 1

Related Questions