set the clock on mysql

Question

I would like to know where does mysql get it current time. I am updating a table and using

update `table1` set `cola` = 1, `colb` =2 , `updated` = NOW();

the time is 2 hours early. the time on the server is completly different, so I'm a little confused...

I tried

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

but I get SYSTEM as result

Answer 1

Try to set time zone on your server: mysql> SET time_zone = '+/-xx:xx';

Answer 2

Possible you find a solution for your need on this thread

When you are not able to set the system time zone by console, you should take a look at the function CONVERT_TZ() .... more informations you get by click the link above

Answer 3

To show used time_zone execute :

mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| time_zone     | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)

You said your time_zone variable is set to SYSTEM, so normally your system and MySQL have the same date.

Check your system date (on linux) :

root@myserv:~# date
mercredi 2 janvier 2013, 15:10:46 (UTC+0100)

Check your MySQL Server date :

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-01-02 15:10:58 |
+---------------------+
1 row in set (0.00 sec)

If the both dates are not the same it's really strange :) if your system have a wrong date, update your system date, if you want to change just MySQL date try :

mysql> set global time_zone='+01:00'

I used '+01:00' because i live in Paris but you should adapt this from your local time zone.

Max.