Select older than 7 days

[jamiet757]

How can I select from a database an entry that is older than 7 days?

 

Right now, the column "data" is what I believe to be a unix timestamp created using this:

 

mktime(date("H"),date("i"),date("s"),date("m"),date("d"),date("Y"))

 

I need to figure out how to phrase the MYSQL query to select something where data > what? if I want it to be greater than 7 days.

[akitchin]

i suppose whoever wrote that line never saw the function time. anyhow, a UNIX timestamp is measured in seconds. in order to get the UNIX timestamp that was exactly 7 days ago, you simply need to subtract the number of seconds in 7 days:

 

echo 'timestamp now is: '.time().'<br />';
echo 'timestamp 7 days ago was: '.(time() - 7*24*60*60);
// 7 days * 24 hours * 60 minutes * 60 seconds

[jamiet757]

so, would this work:

 

$sql="select folder from photos where published=3 and data>".time() - 7*24*60*60;

 

I am not sure if I have my syntax right, but I think that will select folder from photos where published is 3 and data is (the current time) -7 days.

[akitchin]

so, would this work:

 

$sql="select folder from photos where published=3 and data>".time() - 7*24*60*60;

 

I am not sure if I have my syntax right, but I think that will select folder from photos where published is 3 and data is (the current time) -7 days.

 

have you run this to see what you get?

[jamiet757]

No, it didn't work. Here is an example of what is in the "data" field for something that was added a few minutes ago:

 

1264801797

 

The date it gives is Jan 29 2010, 15:49:57

 

Which is correct, so I don't know why the script isn't finding it. I did test the script without the time and it works, so I know that it does what it is supposed to when I don't specify a time.

[jamiet757]

I realized I had my > facing the wrong direction, I want to select where data is smaller than 7 days ago, however it still doesn't work.

 

Do I have to add the time() function anywhere, or is that already built into php?

 

Do I have the sytax right, should it be

 

$sql="delete from photos where published=3 and data<".time() - 7*24*60*60;

 

or do I need to change where the quotes are?

[akitchin]

you need to put parentheses around the subtraction expression. otherwise, php concatenates the current timestamp onto the string, and then attempts to subtract 7*24*60*60 from the string itself.

 

when in doubt, always echo the query to see if it contains what you think it should.

[Genesis730]

$days = time() -  60*60*24*7  // 7 DAYS

$sql="SELECT folder FROM photos WHERE published=3 AND timestamp > $days";

 

maybe this is better??

I wrote it on the fly and isn't tested but looks right to me (most things that fail do work in my head but i'm tryin )

[akitchin]

$days = time() -  60*60*24*7  // 7 DAYS

$sql="SELECT folder FROM photos WHERE published=3 AND timestamp > $days";

 

maybe this is better??

I wrote it on the fly and isn't tested but looks right to me (most things that fail do work in my head but i'm tryin )

 

the row's timestamp should still be LESS THAN the timestamp from 7 days ago in order to be older.

[jl5501]

How about

 

$sql="SELECT folder FROM photos WHERE published=3 AND timestamp < ".strtotime('-7 days');

[jamiet757]

How about

 

$sql="SELECT folder FROM photos WHERE published=3 AND timestamp < ".strtotime('-7 days');

 

That worked like a charm, thanks!