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.
Erik
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
[EMAIL PROTECTED]
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
