Selecting all rows where id is within n days

mjacobson · Post by **mjacobson** » Mon Jul 21, 2008 3:19 pm

I am trying to select all records of a table that the id has been created within the last 10 days. Would the following work?

select id from mytable where id > '10 days'

or is there a better select statement? Should I have an index on id to make this more efficient and if so, what type of index?

Post by **mark** » Mon Jul 21, 2008 3:44 pm

You want
where id > '-10 days'
and a regular index on id to speed it up.

mjacobson · Post by **mjacobson** » Mon Jul 21, 2008 4:07 pm

So my select is OK, I just need to put an index on it.

Post by **John** » Mon Jul 21, 2008 4:55 pm

You need the minus sign in the string, so '-10 days' means 10 days ago. Just '10 days' without the minus sign will not be recognized.

Post by **mark** » Mon Jul 21, 2008 4:56 pm

Not quite. Look closely at my example. You had
'10 days'
I have
'-10 days'

You're comparing the id to a specific point in time and "10 days" is ambiguous. "-10 days" means "10 days ago". "+10 days" would mean "10 days from now". Note that "-10 days" will be the same time of day as "now" but 10 days earlier. If you want the beginning of the day use
'begin of -10 days'