CODEWITHSUNDEEP
phpmysql
user1702837
user1702837

Reputation:

storing time to a db and comparing with current time

i have a simple question, that just needs verification, it's about how time is stored in mysql, here is how i store it:

  if(isset($_SESSION['user']))
{
$u_id=$_SESSION['user'];
//insert current time to db, i set the type of time to TIME 
$query="INSERT INTO time(id,u_id,time) VALUES('NULL','".$u_id."',CURTIME())";
mysql_query($query);
}

that is how i store it, now i would also need to compare it to a value, later on:

 //set current time
$curr_time=time();
//set maximum time to 5min
$max=300; 
//get previous time from db
$query="SELECT * FROM time";
$result=mysql_query($query);
$row=mysql_fetch_array($result); 
$prev_time=$row['time'];
//get difference in time
$diff=$curr_time-$prev_time;
//if max time is surpassed
if($diff>$max)
{
  echo "maximum time surpassed!";

    }

that's the simple code, first of all, is the syntax for inserting the time to the table okay(the CURTIME() thing), also is the comparison fine, will the result stored in $diff yield seconds?thank you, and sorry if you think this question is a waste of time.

Upvotes: 0

Views: 478

Answers (4)

metalfight - user868766
metalfight - user868766

Reputation: 2750

 also is the comparison fine, will the result stored in $diff yield seconds?

You can't directly compare mysql CURTIME() with time() function in PHP.

For example:

In MySql:

CURTIME() will return only time.

select CURTIME();
+-----------+
| CURTIME() |
+-----------+
| 14:15:11  |
+-----------+

In PHP

time() will return current Unix timestamp.

echo time();
1352191547

Look at sberry's answer for compassion.

Upvotes: 0

Undrium
Undrium

Reputation: 2678

Since CURTIME is only on a daily basis and I suspect you want your function to cover the span over days I'd suggest you use NOW() instead as it gives you this format instead:

2012-11-06 09:39:34

Upvotes: 0

Bart Friederichs
Bart Friederichs

Reputation: 33511

Do it in your query:

 $query = "SELECT id FROM time WHERE time + INTERVAL 5 MINUTES > NOW()";

is there are no results, time has passed.

MySQL has terrific time and date handling functions. Use them.

(I'd also add some extra parts in the WHERE clause, if you have more than one record, you'll be looking at the wrong one probably.)

Upvotes: 1

sberry
sberry

Reputation: 132018

CURTIME() will only give you the time, you likely want NOW() which is the date and time. Then you will need to use strtotime to convert the saved value from the database to seconds since epoch.

Upvotes: 3

Related Questions