PHP and Mysql Date Question

fluiduk · 23 Nov 2006 at 15:51

Hi All

I am trying to make a query that will recall all records from a database where an mysql date field is 7 days earlier than the current date. Ie 7 days have passed since the date in the field.

Is there a simple way to do this?

Any advice appreciated

Thanks
Aaron

fluiduk · 23 Nov 2006 at 16:27

Maybe this will help someone, but its just plain confusing me > http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

robmiller · 23 Nov 2006 at 16:42

Why's that confusing :/

Code:

SELECT *
FROM table
WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date

Where `date` is your DATE/DATETIME-formatted column and `table` is your table.

fluiduk · 23 Nov 2006 at 16:45

Because I am not as smart as you Rob

Thanks once again.. don't know what I would do without you

Thanks

fluiduk · 23 Nov 2006 at 17:08

Although it doesn't quite work

This is the code

Code:

SELECT *
FROM returns
WHERE DATE_SUB(CURDATE(), INTERVAL -3 DAY) <= date_collection_email

I have a record where the date is 2006-11-19 and it doesn't show up. If I change the -3 to just 3, it shows all records.

Any ideas?

Thanks
Aaron

robmiller · 23 Nov 2006 at 17:30

What are you trying to do here? Get the ones from the last 3 days?

fluiduk · 23 Nov 2006 at 22:37

Trying to get ones that are older than 3 days, ie 3 days or more have passed since the date in the field.

Thanks Rob

Aaron

robmiller · 24 Nov 2006 at 00:36

Just change the <= to a >= to get ones three or more days older, or > to get ones 4 or more days older.