update records where timestamp is older than ten days

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
petroz
Forum Newbie
Posts: 16
Joined: Sun Sep 06, 2009 11:56 pm

update records where timestamp is older than ten days

Post by petroz »

Hi Guys,

I have a cron job running that is supposed to update all records where the 'time' field is older that ten days. It seems to updating the entries within just a few hours of the timestamp instead of ten days. Please help!!

Code: Select all

<?php
 
include 'db.php';
 
$time = date("YmdHis");
 
$requests = "UPDATE requests SET status=4 WHERE time <= ($time-864000)";
 
$requestsresult = mysql_query($requests)
    or die("Invalid remove query: " . mysql_error());
 
?>
Thanks,
P
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: update records where timestamp is older than ten days

Post by Mirge »

petroz wrote:Hi Guys,

I have a cron job running that is supposed to update all records where the 'time' field is older that ten days. It seems to updating the entries within just a few hours of the timestamp instead of ten days. Please help!!

Code: Select all

<?php
 
include 'db.php';
 
$time = date("YmdHis");
 
$requests = "UPDATE requests SET status=4 WHERE time <= ($time-864000)";
 
$requestsresult = mysql_query($requests)
    or die("Invalid remove query: " . mysql_error());
 
?>
Thanks,
P
I assume that the "time" field in SQL is a UNIX timestamp since you didn't mention it. You should calculate the timestamp outside of the SQL query, not in it. And to get a UNIX timestamp that is 10 days ago I would use something like:

Code: Select all

 
$timestamp = strtotime(date("M d, Y", strtotime("-10 days")) . " 12:00:00 AM");
print $timestamp; // Example, outputs: 1252299600 currently
 
And then use that in your query.
petroz
Forum Newbie
Posts: 16
Joined: Sun Sep 06, 2009 11:56 pm

Re: update records where timestamp is older than ten days

Post by petroz »

Hi Mirge,

My timestamps are stored in mysql as "2009-09-16 23:22:06." If I am correct, that is not a Unix timestamp. What would be the best way to work with this type of timestamp?

Thanks,
P
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: update records where timestamp is older than ten days

Post by Mirge »

petroz wrote:Hi Mirge,

My timestamps are stored in mysql as "2009-09-16 23:22:06." If I am correct, that is not a Unix timestamp. What would be the best way to work with this type of timestamp?

Thanks,
P
In mysql, type: DESCRIBE requests;

Paste the results here please.
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: update records where timestamp is older than ten days

Post by Mirge »

This is what I just threw together to grab the DATE in the format you specified in PHP:

Code: Select all

 
<?php
$timestamp = strtotime(date("M d, Y", strtotime("-10 days")) . " 12:00:00 AM");
$date = date("Y-m-d H:i:s", $timestamp);
 
print "Timestamp: $timestamp\n";
print "Date: $date\n";
?>
 
Outputs:
Timestamp: 1252299600
Date: 2009-09-07 00:00:00
 
Try using that code, and if the field is a MySQL "DATE" field, I believe you should be able to compare using the same method you had in your OP... compare against $date, which is 10 days prior to current date..
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: update records where timestamp is older than ten days

Post by John Cartwright »

Code: Select all

... WHERE date > DATE_SUB(NOW(), INTERVAL 10 DAYS)
petroz
Forum Newbie
Posts: 16
Joined: Sun Sep 06, 2009 11:56 pm

Re: update records where timestamp is older than ten days

Post by petroz »

Hi John,

I tried adding that in, but I am getting a syntax error. Please let me know where I am going wrong.

Here is the error...

Invalid remove query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DAYS)' at line 1

Here is the entry in the db for 'time'.

2009-09-16 23:22:06

Code: Select all

<?php
 
include 'db.php';
 
$time = date("YmdHis");
 
$requests = "UPDATE requests SET status=4 WHERE time > DATE_SUB(NOW(), INTERVAL 10 DAYS)";
 
$requestsresult = mysql_query($requests)
    or die("Invalid remove query: " . mysql_error());
 
?>
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: update records where timestamp is older than ten days

Post by John Cartwright »

Sorry, it's "INTERVAL 10 DAY" (not DAYS)
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: update records where timestamp is older than ten days

Post by Mirge »

Ok don't try mine then :roll:
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: update records where timestamp is older than ten days

Post by John Cartwright »

Mirge wrote:Ok don't try mine then :roll:
It's worth noting you should not generally be comparing PHP and mysql dates, because they are not guaranteed to be the same date/time.
petroz
Forum Newbie
Posts: 16
Joined: Sun Sep 06, 2009 11:56 pm

Re: update records where timestamp is older than ten days

Post by petroz »

Mirge wrote:Ok don't try mine then :roll:
Sorry Mirge... I am trying everything thrown at me... I was having trouble understanding how to implement what you were explaining.

To your question.. Explain requests... Here is the structure.

request int(18) No auto_increment
req_uid varchar(41) utf8_unicode_ci No
resp_uid varchar(41) utf8_unicode_ci No
status tinyint(1) No
req_nick varchar(20) utf8_unicode_ci No
resp_nick varchar(20) utf8_unicode_ci No
time datetime No
req_image varchar(100) utf8_unicode_ci Yes NULL
resp_image varchar(100) utf8_unicode_ci Yes NULL
req_description varchar(255) utf8_unicode_ci Yes NULL
resp_description varchar(255) utf8_unicode_ci Yes NULL


If you could... Please show me how I can change my code to use your example.

Code: Select all

<?php
 
include 'db.php';
 
$time = date("YmdHis");
 
$requests = "UPDATE requests SET status=4 WHERE time <= ($time-864000)";
 
$requestsresult = mysql_query($requests)
    or die("Invalid remove query: " . mysql_error());
 
?>
Thanks,
P
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: update records where timestamp is older than ten days

Post by Mirge »

petroz wrote:
Mirge wrote:Ok don't try mine then :roll:
Sorry Mirge... I am trying everything thrown at me... I was having trouble understanding how to implement what you were explaining.

To your question.. Explain requests... Here is the structure.

request int(18) No auto_increment
req_uid varchar(41) utf8_unicode_ci No
resp_uid varchar(41) utf8_unicode_ci No
status tinyint(1) No
req_nick varchar(20) utf8_unicode_ci No
resp_nick varchar(20) utf8_unicode_ci No
time datetime No
req_image varchar(100) utf8_unicode_ci Yes NULL
resp_image varchar(100) utf8_unicode_ci Yes NULL
req_description varchar(255) utf8_unicode_ci Yes NULL
resp_description varchar(255) utf8_unicode_ci Yes NULL


If you could... Please show me how I can change my code to use your example.

Code: Select all

<?php
 
include 'db.php';
 
 
$timestamp = strtotime(date("M d, Y", strtotime("-10 days")) . " 12:00:00 AM");
$date = date("Y-m-d H:i:s", $timestamp); 
 
$requests = "UPDATE requests SET status=4 WHERE `time` <= '$date'";
 
$requestsresult = mysql_query($requests)
    or die("Invalid remove query: " . mysql_error());
 
?>
Thanks,
P

Edited the code above..
petroz
Forum Newbie
Posts: 16
Joined: Sun Sep 06, 2009 11:56 pm

Re: update records where timestamp is older than ten days

Post by petroz »

Works perfectly Mirge!!

Thanks a bunch!
P
User avatar
Mirge
Forum Contributor
Posts: 298
Joined: Thu Sep 03, 2009 11:39 pm

Re: update records where timestamp is older than ten days

Post by Mirge »

petroz wrote:Works perfectly Mirge!!

Thanks a bunch!
P
No worries.
Post Reply