|
Home > Archive > MySQL Server Forum > August 2005 > Date range sorting
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Date range sorting
|
|
| jgabbai@gmail.com 2005-08-15, 8:23 pm |
| 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 2005-08-15, 8:23 pm |
| jgabbai@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@gmail.com 2005-08-15, 8:23 pm |
| Wow! That was really helpful! Many thanks!!
|
|
|
|
|