Date range sorting

[jgabbai]

Hi,

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

Today,
(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
SQL?

[Bill Karwin]

jg*****@gmail.com wrote:

Hi,

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

Today,
(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

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;

Regards,
Bill K.

[jgabbai]

Wow! That was really helpful! Many thanks!!