timestamp , what's wrong ?

[asmith]

<?php
$conn = mysql_connect("localhost","john","qwerty");
mysql_select_db("test5",$conn);
$offset = '60'; // 1 min
$now = time();
$sql = "DELETE FROM azxc
        WHERE aaa + $offset < $now
       ";
if ( !$result = mysql_query($sql) )
{echo "no";}
else {echo "yes";
    
}
?>

 

 

aaa is timestamp ,  azxc the table name,

the out put is yes,

but when i check mysql data, nothing has changed !

 

aaa                              id

2007-12-04 11:08:02      1

2007-12-04 11:08:02      2

 

 

what is wrong with the code ?

 

 

 

[rajivgonsalves]

You can use the UNIX_TIMESTAMP function in mysql itself

 

"DELETE FROM azxc
        WHERE (UNIX_TIMESTAMP(aaa) + $offset) < UNIX_TIMESTAMP(now())
       ";

 

not too sure it will work but you can try it up since your doing a delete operation please backup your DB before you go ahead with it.

[PHP_PhREEEk]

The code you have will work without any fuss if you store the original timestamp as a Unix timestamp instead of a MySQL or other formatted timestamp. Unix is the native timestamp, and is easiest to perform operations on. Store it, compare it, pull it, but don't bother formatting it until it needs to be presented to the browser. Then just use date() to present it to a user. Simple...

 

PhREEEk

[PHP_PhREEEk]

For the field in MySQL where you are storing your timestamp, alter it to be int(11), not null

 

When you store a record, just insert time() into that field. When you want to use the above code to delete records that have expired, it will work as is.

 

PhREEEk

[asmith]

well i fixed it ,

the problem was i record id 1 , 2 in mysql , which its time is the time of my windows, (GMT + 3:30 )

but in php code, the date()  time is in GMT ,  so  it wasn't time! i changed the date() in php to "now()"  which is mysql , and worked out !

2 question poped up  !

1. how can i change the mysql time to GMT .

2. mysql store the time like  YYYY-MM-dd HH:mm:ss, 

it is for timestamp , can i compare a value with this type which is recorded in a varchar field to a timestamp record ?

[PHP_PhREEEk]

You can do anything you want with timestamps and date formatting... but it is all a bunch of wasted overhead programming. I told you above how to make a timestamp field and just store a native Unix stamp. Your database does not care one bit how pretty your timestamp is when formatted, so store it as Unix and format for someone with a browser who can appreciate a nice formatting.

 

PhREEEk