php time?

DizzyD · 06-10-2009, 06:49 PM

So I have a video id and at timestamp stored in a database. I'm created a cron.php to pull the data in a while() loop and compare the time w/ the current time. If it's been 8hrs or more, then the row is to be deleted, if not, then nothing happens. In theory it makes sense, but i'm unsure how to achieve the time math. Here what I'm working with so far:

Code:

$sql = "SELECT * FROM credits_temp";
$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)) {

	//Database time
	$dataBaseTime = $row['time'];
	$curTime	= '2009-06-09 20:54:13';//not sure how to use date for this time format...

	$difference = "i have no idea"

	if( $difference >= "8hrs somehow")
	{
		//delete the row
	}



}

Any help or guidance would be most helpful. Thanks :D

Dave

Village Idiot · 06-10-2009, 07:01 PM

Is your database storing these dates as a string? What field type is the date stored in?

PHP generally uses whats called a Unix Timestamp to do time. A Unix Timestamp is the number of seconds from 1/1/1970 (aka the Unix Epoch). This provides easy means to get the date and difference them.

To get the current date, use:

PHP Code:


			
time()

Use these various method to get differences

PHP Code:


			
$minute = 60; 
$hour = $minute*60;
$day = $hour*24;
$year = $day*365;

//Eight hours from now
$8hrFromNow = time()+($hour*8);

//Eight hours ago (the rows timestamp must be equal or lower than this number)
$8hrAgo = time()-($hour*8);

To format these timestamps, use Date

The reason I bring this up is because the DB might be spitting out a formatted timestamp at you. If you can give us the column type I can tell you how to work with it.

DizzyD · 06-10-2009, 07:12 PM

Thanks for the quick response. The database is set to automatically set the time/date when the entry is made (CURRENT_TIMESTAMP). So the format is set like so:

2009-06-09 20:54:13

So yes I pull it as a string,

$row['time'] displays "2009-06-09 20:54:13"

So i need to get the current time in that same format I would assume, then find the difference of the 2. If it's 8 or more, perform action.

Village Idiot · 06-10-2009, 07:18 PM

Quote:

Originally Posted by DizzyD

Thanks for the quick response. The database is set to automatically set the time/date when the entry is made (CURRENT_TIMESTAMP). So the format is set like so:

2009-06-09 20:54:13

So yes I pull it as a string,

$row['time'] displays "2009-06-09 20:54:13"

So i need to get the current time in that same format I would assume, then find the difference of the 2. If it's 8 or more, perform action.

What data type is it? I would assume timestamp if that is the default value. If that is corrent, that means that you are getting the a formatted timestamp opposed to a string. Have a query something like this

Code:

SELECT * FROM db WHERE DATESUB(CURDATE(),INTERVAL 8 HOURS)>=insert_time

Anything that query returns will be more than eight hours old. insert_time being the column name of your timestamp. This code is not tested, but should work.

Village Idiot · 06-10-2009, 07:19 PM

Above code fixed.

DizzyD · 06-10-2009, 07:27 PM

So if i'm reading this correctly, this should only pull rows that are 8+ hours?

Village Idiot · 06-10-2009, 07:29 PM

That is correct.

DizzyD · 06-10-2009, 07:30 PM

and insert_time is that the table name? As in my case it would just be "time"?

Village Idiot · 06-10-2009, 07:31 PM

Quote:

Originally Posted by DizzyD

and insert_time is that the table name? As in my case it would just be "time"?

insert_time is whatever you named the column storing the timestamp. DB is poorly named, it is the table (I don't know why I put db as the name).

DizzyD · 06-10-2009, 07:33 PM

Wonderful! Thanks so much for the help! If I run into problems, i'm sure i'll be back. Wish me luck!

Thanks again!

Village Idiot · 06-10-2009, 07:35 PM

Always glad to help.

DizzyD · 06-10-2009, 07:38 PM

Well I knew it wouldn't be long. I'm getting "supplied argument is not a valid MySQL result resource" errors and i'm assuming it is because of the sql statement. Is it a caps lock issue or something? I have no idea..

Here's what i'm working with:

Code:

$sql = "SELECT * FROM credits_temp WHERE DATESUB(CURDATE(),INTERVAL 8 HOURS)>=time";
$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)) {

echo $row['time'];
echo "<br />";

}

Village Idiot · 06-10-2009, 07:47 PM

Quote:

Originally Posted by DizzyD

Well I knew it wouldn't be long. I'm getting "supplied argument is not a valid MySQL result resource" errors and i'm assuming it is because of the sql statement. Is it a caps lock issue or something? I have no idea..

Here's what i'm working with:

Code:

$sql = "SELECT * FROM credits_temp WHERE DATESUB(CURDATE(),INTERVAL 8 HOURS)>=time";
$query = mysql_query($sql);

while ($row = mysql_fetch_array($query)) {

echo $row['time'];
echo "<br />";

}

Case has no bearing in an SQL query unless you are searching for a case-sensitive value. It is general practice to caps SQL commands and keywords.

Run this:

Code:

$sql = "SELECT * FROM credits_temp WHERE DATESUB(CURDATE(),INTERVAL 8 HOURS)>=time";
$query = mysql_query($sql);

$row = mysql_fetch_array($query) or die(mysql_error());

This will output the error returned.

DizzyD · 06-10-2009, 07:55 PM

bah, I guess I should of done that from the start anyway :P

This is the error it spit out:

Quote:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/dave/public_html/dizzyd/dev1/cron.php on line 21
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 'HOURS)>=time' at line 1