mysql NOW() return wrong value whereas global timezone is set +00:00

Question

since 2 weeks I puzzled over timezone issue, everything working fine on my localhost BUT it returns wrong value on dedicated server. Let me tell what i have done so far.

First set global timezone by below query: ( Super privilege both on localhost and server )

 SET GLOBAL time_zone = '+00:00';

now run below query to cross check whatever done

SELECT NOW(),@@global.time_zone AS gtz,@@session.time_zone AS stz,
       TIMEDIFF(NOW(), CONVERT_TZ( NOW() , @@session.time_zone ,  '+00:00' ) )
       AS OFFSET;

but it display different results on local and dedicated server

on localhost (192.168.x.x) mysql version : 5.5.8

+---------------------+--------+--------+----------+
| NOW()               | gtz    | stz    | OFFSET   |
+---------------------+--------+--------+----------+
| 2012-07-02 07:06:55 | +00:00 | +00:00 | 00:00:00 |
+---------------------+--------+--------+----------+
1 row in set (0.00 sec)

on dedicated server (182.168.x.x) mysql version :5.1.53-log

+---------------------+--------+--------+----------+
| NOW()               | gtz    | stz    | OFFSET   |
+---------------------+--------+--------+----------+
| 2012-07-02 12:37:59 | +00:00 | +00:00 | 00:00:00 |
+---------------------+--------+--------+----------+

My question is

why NOW() gives wrong time ( above is IST ) whereas timezone is set to +00:00 ?

side note :

I run below query

SHOW VARIABLES LIKE '%time%zone%';

on localhost

+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | +00:00              |
+------------------+---------------------+

on server

+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | GMT+5               |
| time_zone        | +00:00              |
+------------------+---------------------+

does this will affect the result? OR

is there any bug in earlier version of mysql ?

please help me.

Answer 1

When calling NOW() (and related functions), MySQL converts the computer's system clock to the session timezone:

• If the system clock is set to 12:30+05:30 and the session timezone is +00:00, the result will be 07:00.

• If the system clock is set to 17:30+05:00 and the session timezone is +00:00, the result will be 12:30.

However, one can 'fool' MySQL into thinking that the system clock is in a different timezone to that which the operating system believes by using the --timezone command line argument to mysqld_safe:

• If the system clock is set to 17:30+10:30 and the session timezone is +00:00, but MySQL was started in such a way specifying that the system clock should be understood to be GMT+5, the result will be the same as the second bullet above.

You should therefore check:

1. That the timezone of the system clock reconciles with the value given in the system_time_zone system variable (if it doesn't, ensure that you are not specifying a --timezone argument to mysqld_safe and that there was no TZ environment variable set when mysqld_safe was invoked);

2. That the system clock is reporting the correct time in its specified timezone.