Page 1 of 1
update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 2:26 pm
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
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 2:39 pm
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.
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 2:42 pm
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
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 2:44 pm
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.
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 2:47 pm
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..
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 4:23 pm
by John Cartwright
Code: Select all
... WHERE date > DATE_SUB(NOW(), INTERVAL 10 DAYS)
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 4:31 pm
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());
?>
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 4:38 pm
by John Cartwright
Sorry, it's "INTERVAL 10 DAY" (not DAYS)
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 4:39 pm
by Mirge
Ok don't try mine then

Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 4:40 pm
by John Cartwright
Mirge wrote:Ok don't try mine then

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.
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 4:57 pm
by petroz
Mirge wrote:Ok don't try mine then

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
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 5:03 pm
by Mirge
petroz wrote:Mirge wrote:Ok don't try mine then

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..
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 5:13 pm
by petroz
Works perfectly Mirge!!
Thanks a bunch!
P
Re: update records where timestamp is older than ten days
Posted: Thu Sep 17, 2009 5:16 pm
by Mirge
petroz wrote:Works perfectly Mirge!!
Thanks a bunch!
P
No worries.