Selecting between dates with different timestamps

[fnord]

I'm in a bit of a mess here, trying to deal with some timestamp
issues.

I need to select items from a database with a datetime timestamp:

0000-00-00 00:00:00

I need to select these items based on several different criteria, for
which I will use separate select statements, but some of them are a
bit confusing.

I need to select items that are less than 24 hours old, between 24 and
48 hours old, between 3 to 5 days olf, between 6 to 10 days old,
between 11 to 30 days old, and items older than 30 days.

In the past when comparing times I have always converted the database
timestamp to Unix time, set a nowTime variable to current time in unix
time, and done the addition and subtraction. In this case, that
doesn't necessarily work, as I need to use "BETWEEN" in the
timestamps, and I'd really rather handle the separation of times with
the select statement than in the code.

Can anyone point me in the right direction, I'm not sure what the
select statement should look like.
Thanks,
fnord

[Aggro]

fnord wrote:

I need to select items that are less than 24 hours old, between 24 and
48 hours old, between 3 to 5 days olf, between 6 to 10 days old,
between 11 to 30 days old, and items older than 30 days.

All in one query, or one criteria for each query? Perhaps something like
this:

select *
from devices
where date_ between
date_sub( now(), INTERVAL 30 DAY )
and date_sub( now(), INTERVAL 11 DAY );

Instead of "between", you can also use normal > < = comparison
characters, like this:

select *
from devices
where
date_ >= date_sub( now(), INTERVAL 30 DAY )
and date_ <= date_sub( now(), INTERVAL 11 DAY );

After INTERVAL you give the number and after that DAY, HOUR, SECOND,
etc. Read full list of choises from DATE_SUB() description from:

http://dev.mysql.com/doc/mysql/en/Da...functions.html