I am working with a table that has a DATETIME type field which gets updated after the PHP datetime default timezone is set:
date_default_timezone_set("Europe/London");
$date=date("Y-m-d H:i:s");
mysql_query("UPDATE mytable SET status='$status', statusupdated='$date' WHERE id='$id'");
I need to create a query that gets all the results after a certain ($landing) time (UTC):
mysql_query("SELECT * FROM mytable WHERE CONVERT_TZ(`statusupdated`, 'GMT', 'UTC') > $landingtime");
However the database doesn't have any timezone tables so the above doesn't work.
How can I standardise the query so I can compare statusupdated against UTC?
NOTES:
I am unable to edit the database data types as this is an external database. Thanks
To simplify the question I have simplified the code (I know about SQL injection etc) and I know about the deprecated functions. Thanks.
Worked out how to do this.
I can convert @@session.time_zone to '+00.00'. Lovely!
mysql_query("SELECT * FROM mytable WHERE CONVERT_TZ(`date`, @@session.time_zone, '+00:00') > $landingtime");