MySQL and PHP TimeZone

Question

In my PHP.ini file I set the TimeZone like so...

'America/New_York'

so when i ran a simple php Date() function

echo date("Y-m-d H:i:s");

I get the Correct dateTime according to 'MY' system time as that's what i am comparing against and want to store in my MySQL db as well. (as reported from PHP).

Now the problem is, i exported a MySQL db to PDF format, just to see what it looked like, and the time was 1 hour back, ex.. it was (10:00 a.m.) here and the PDF footer said (9:00 a.m.)

So.. i got to thinking.. my PHP script will INSERT into the db the correct dateTime that i need.. But i have alot of dateTime comparing going on for accounts,

I know if i run any MySQL Queries in phpMyAdmin then i will get the wrong dateTime.

i have tried running in (phpMyAdmin SQL Query)

SET time_zone = 'America/New_York';
-and-
SET time_zone = '-05:00';

But when I run the query

SELECT @@global.time_zone, @@session.time_zone;

I get back SYSTEM and SYSTEM.

(I should also mention i am on shared hosting)

If I use the MySQL NOW() function in my query, the time entered into the db will be calculated by MySQL, according to it's own timezone.

like this..

mysql_query("INSERT INTO table (id, value, time_created) 
                        VALUES ('{$id}', '{$value}', NOW())");

I understand that I will have to do all of my INSERTING and comparing in PHP to keep the times right.. but with the above, it will insert the wrong time.

so this is my dilemma...

But will this affect anything that i am not foreseeing? I just feel like somehow this will affect my times.

So how can I get around this or get MySQL on the SAME timezone? and make sure that ALL my date/times are right, not the 1 hour behind..

Answer 1

Two queries ran successively through PhpMyAdmin will be executed in two separate sessions (connections) therefore SELECT @@session.time_zone alone will always return "SYSTEM".

I doubt you can (and I hope you cannot) change the global time zone on a shared server, so always expect "SYSTEM" for SELECT @@global.time_zone. On the other hand you should be able to change your session's time zone.

Try running these two queries in one execution, it should show the new time zone :

SET time_zone = '-05:00'; SELECT @@session.time_zone; -- same session

NOW() returns time in the current session time zone, so time zone does matter. However I would rather store times in GMT time zone, but I suppose that's more a matter of taste.