CODEWITHSUNDEEP
mysqlsql
anthonyms
anthonyms

Reputation: 948

MySql date_add() query improvement

I am running a query that gets today's activity as at now() and compares it to activity at the same time for the past 5 days.

select count(*) ,date(timestamp) date , now() now  
from activitylog 
where timestamp>date_add(now(), INTERVAL -5 DAY) and 
      hour(timestamp) <=hour(now()) 
group by date(timestamp) 
order by date(timestamp) desc;

Is there a way of doing it more efficiently than the above way?

Upvotes: 0

Views: 334

Answers (1)

user359040
user359040

Reputation:

As written, the query will return the day's activity up to the current hour for the current day and the previous 4 days, plus the current hour's activity only for five days ago - to make it include the whole day to the current hour for the fifth day, change the where clause to:

where timestamp>date(date_add(now(), INTERVAL -5 DAY)) and 
      hour(timestamp) <=hour(now())

Apart from this, the only issue I can think of is to check that there is an index on the timestamp column. Otherwise, it appears to be in its optimum form.

Upvotes: 1

Related Questions