CODEWITHSUNDEEP
phpmysql
Hard worker
Hard worker

Reputation: 4046

How to query this column so that only entries from within the last week are taken

I have column called 'last updated' of this format: 2009-10-13 09:20:30 and I want to select all entries in the column that have a date that is within the last 7 days of the time the query is ran. I am using php in the backend for this query along with mysql.

I am constructing a large query, this will be part of it.

Thanks very much if you can help me.

Upvotes: 1

Views: 66

Answers (3)

liquorvicar
liquorvicar

Reputation: 6106

It depends on exactly what you want and whether you care about the time part. If the current time is, say, 5pm, and you want all entries from the day 7 days ago (and not just the entries from 5pm on the day 7 days ago) you will need to make the query a bit more complex.

SELECT *
FROM `yourtable` AS t1
WHERE DATE( t1.last_updated ) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

In your example, this query will give you all entries since 2009-10-06 00:00:00 rather than entries since 2009-10-06 09:20:30 which is what you will get if you compare your column directly with DATE() less 7 days.

Upvotes: 0

Bjoern
Bjoern

Reputation: 16304

A short example on how to do this:

SELECT *
FROM yourtable AS t1
WHERE t1.last_updated > DATE_SUB(NOW(), INTERVAL 7 DAY);

The documentation can be found here in the MySQL manual.

Upvotes: 1

dwalldorf
dwalldorf

Reputation: 1379

From MySQL Docu: SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col

Upvotes: 0

Related Questions