Date range sorting

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View

I am wondering how to return data by date ranges, where I return all
data made:

(Every individual day of the week up to 1 week ago - eg. Everything for
last Monday, last Tuesday etc)
Two/Three weeks ago
Anything over three weeks ago

Also, is this something best done in PHP after a full query or during

Re: Date range sorting wrote:
Quoted text here. Click to load it

create table a ( d date);

insert into a values
   ('2005-08-15'), ('2005-08-12'), ('2005-08-11'),
   ('2005-08-05'), ('2005-07-29'), ('2005-06-15');

select *, case
   when to_days(curdate()) - to_days(d) < 7
     then to_days(curdate()) - to_days(d)
   when to_days(curdate()) - to_days(d) between 7 and 21
     then 14
   else 21 end as dayCategory
from a
order by dayCategory;

If you need an aggregate, such as the count of such records per category:

select count(*), ... as dayCategory
from a
group by dayCategory;

Bill K.

Re: Date range sorting

Wow! That was really helpful! Many thanks!!

Site Timeline