On Friday, April 19, 2002, at 01:23  PM, Richard Fox wrote:

> I want to compare a timestamp(14) field in a MySQL table with the 
> current
> time. Currently I use the following pseudo-sql:
> select field from table where table.time <  date("YmdHis", time())
> only, the table timestamp is GMT time and the time function is returning
> local time. How do I convert time() to GMT time?

Okay, I have to leave work soon so I dug up the original post.  Sorry if 
I made you repost your code for nothing.

You have a timestamp(14) MySQL column:  YYYYMMDDhhmmss
You want to compare a value in this column with the current time.

$sql = "SELECT table.field
         FROM   table
         WHERE  table.time < NOW()";
$result = mysql_query($sql, $db);

You don't want to use PHP to do the comparison, it's much faster to let 
the database itself do the comparison.  This saves going back and forth 
between PHP and MySQL.

HOWEVER ... you've thrown a variable into the mix -- the MySQL TIMESTAMP 
column is in GMT.  If MySQL's internal time zone is GMT, then it's no 
problem.  But I have a feeling this isn't the case, since you said that 
PHP is returning local time.  So you can't do it the way I did it above, 
you've got to compensate for the GMT.

I don't know where you live, so it's your responsibility to find out the 
number hours difference between your time zone and GMT.  Let's say it's 
3 hours less than GMT (which I think would mean you live in the Atlantic 
ocean, but it's just an example).

3 hours X 60 mins X 60 seconds = 10800 seconds

Now, simply convert the table.time value to a Unix-style timestamp, 
which is an integer representing the number of seconds since 1970 (I 
think).  Then, add 10800 to this number, so that the server time zone 
and the TIMESTAMP column are equivalent.  Now perform the comparison.

$sql = "SELECT table.field
         FROM   table
         WHERE  UNIX_TIMESTAMP(table.time) + 10800 < NOW()";
$result = mysql_query($sql, $db);
echo "<ul>\n";
while ($row = mysql_fetch_assoc($result)) {
   echo "<li>" . $row['field'] . "</li>\n";
echo "</ul>\n";

HTH, I'm gone for the weekend.


PS: if my math was wrong or I went in the wrong direction, sorry, but do 
some playing around with what I've showed you and you should be able to 
figure it out.


Erik Price
Web Developer Temp
Media Lab, H.H. Brown

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to