CODEWITHSUNDEEP
mysqlsql
GPecchio
GPecchio

Reputation: 313

Get hourly result for count in the last 48 hours - MySql

I need a query that returns the count every hour for the last 48 hours. Until now I tried either:

SELECT count(*), date_format(created, '%H:%i - %d/%m/%y') as datecreated
FROM mimesi_indexer.served_clips
where created > NOW() - INTERVAL 24 HOUR
group by floor(hour(created))
order by created ASC

and:

SELECT count(*), date_format(created, '%H:%i - %d/%m/%y') as datecreated
FROM mimesi_indexer.served_clips
where created > NOW() - INTERVAL 48 HOUR
group by floor(hour(created))
order by created ASC

But neither of those give the wanted result. Do you have any idea on how I could do this? Thanks

Upvotes: 0

Views: 370

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

If you want every hour, don't you want this:

SELECT count(*), date_format(created, '%H - %d/%m/%y') as datecreated
FROM mimesi_indexer.served_clips
where created > NOW() - INTERVAL 24 HOUR
group by date_format(created, '%H - %d/%m/%y')
order by min(created) ASC;

That is, you need to include the day as well as the hour -- and remove the minutes from datecreated.

Upvotes: 2

Related Questions