469,112 Members | 2,026 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

Selecting between dates with different timestamps

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
Jul 20 '05 #1
1 2777
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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

20 posts views Thread by Sims | last post: by
3 posts views Thread by jrc4728 | last post: by
8 posts views Thread by Angelos | last post: by
10 posts views Thread by Craig Wahlmeier | last post: by
2 posts views Thread by p175 | last post: by
3 posts views Thread by MaRCeLO PeReiRA | last post: by
9 posts views Thread by Bosconian | last post: by
2 posts views Thread by Jim Carlock | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.