TalkPHP - php time?

Page 1 of 2

Show 40 post(s) from this thread on one page

TalkPHP (http://www.talkphp.com/forums.php)

- General (http://www.talkphp.com/general/)

- - php time? (http://www.talkphp.com/general/4527-php-time.html)

DizzyD

06-10-2009 06:49 PM

php time?

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 (Post 25169)

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 (Post 25174)

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 (Post 25178)

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

Village Idiot

06-10-2009 07:58 PM

Change HOURS to HOUR

DizzyD

06-10-2009 08:01 PM

Hmm... Still no dice. Exact same error.

Village Idiot

06-10-2009 08:11 PM

What is the data type of time? I still haven't gotten a clear answer to that.

Village Idiot

06-10-2009 08:12 PM

The command is not DATESUB, its DATE_SUB. I can't believe I missed that, I'm a little rusty on my MySql.

DizzyD

06-10-2009 08:18 PM

I apologize for that, here is a screenshot of the db in phpmyadmin:

DizzyD

06-10-2009 08:21 PM

Ahhh, good timing... just after a bit of research, I came across that as well. Thanks again for your help :D

All times are GMT. The time now is 03:38 PM.

Page 1 of 2

Show 40 post(s) from this thread on one page