CODEWITHSUNDEEP
sql
Rox
Rox

Reputation: 2917

Get the highest value of the last 7 days with SQL

I would like to fetch the highest value (from the column named value) for the 7 past days. I have tried with this sql:

SELECT MAX(value) as value_of_week 
FROM events 
WHERE event_date > UNIX_TIMESTAMP() -(7 * 86400);

But it gives me 86.1 that is older than 7 days from today´s date. Given the rows below, I should get 55.2 with date 2014-05-16 07:07:00.

id  value   event_date
1   28.     2014-04-18 08:23:00
2   23.6    2014-04-22 06:43:00
3   86.1    2014-04-29 05:32:00
4   43.3    2014-05-03 08:12:00
5   55.2    2014-05-16 07:07:00
6   25.6    2014-05-19 06:11:00

Upvotes: 1

Views: 2027

Answers (3)

Ahmad Abuhasna
Ahmad Abuhasna

Reputation: 291

you can use

SELECT MAX(value) as value_of_week FROM events where event_date>= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY AND event_date< curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY;

Upvotes: 0

Tommy Ivarsson
Tommy Ivarsson

Reputation: 605

Im guessing this is MySQL and in that case you could do this:

select max(value) as value_of_week from events where event_date between date_sub(now(),INTERVAL 1 WEEK) and now();

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You are comparing unix time stamps to date. How about this?

SELECT MAX(value) as value_of_week
FROM events
WHERE event_date > date_add(now(), interval -7 day);

Upvotes: 6

Related Questions