How do I make sure that BOTH my server and MYSQL have the same timezone?

Because when I create a time in Python scripts..I want it to match MYSQL's time.

How do I make BOTH of them pacific time?

Solution 1:

You don't mention an OS but for RedHat derived it should be system-config-time to setup your timezone. For MySQL read this URL:

http://dev.mysql.com/doc/refman/5.1/en/mysql-tzinfo-to-sql.html

To summarize, I had to load the timezones from the OS:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Add the following line to ```/etc/my.cnf` in the mysqld section and restart:

default-time-zone='Pacific/Honolulu'

And Bob is your uncle:

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| Pacific/Honolulu   | Pacific/Honolulu    | 
+--------------------+---------------------+
1 row in set (0.00 sec)

I tried the following test script:

import MySQLdb
import os
import re
import time
import datetime
from datetime import date, datetime, time

db = 'test'
dbhost = 'localhost'
dbport = 3306
dbuser = 'test'
dbpass = 'test'

start_time = datetime.today()

con = MySQLdb.connect(host=dbhost, port=dbport, user=dbuser, passwd=dbpass, db=db)
cursor = con.cursor()
sql = "SELECT current_time;"
cursor.execute(sql)
list = cursor.fetchall()
con.close()

print "------------------------------------------------------------------------------------"
print "Python time is: "
print start_time
print
print "MYSQL time is:"
for result in list:
    print result[0]
print "--

----------------------------------------------------------------------------------"

which output:

------------------------------------------------------------------------------------
Python time is: 
2010-03-30 22:29:19.358184

MYSQL time is:
22:29:19
------------------------------------------------------------------------------------

Do you have the timezone table populated:

mysql> use mysql;
mysql> select count(*) from time_zone;
+----------+
| count(*) |
+----------+
|     1678 | 
+----------+
1 row in set (0.00 sec)