Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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 themysqld`` 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)
share|improve this answer

On my servers, I leave time_zone set to "SYSTEM" as described here:

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

The global time_zone system variable indicates the time zone the server currently is operating in. The initial value for time_zone is 'SYSTEM', which indicates that the server time zone is the same as the system time zone.

share|improve this answer
    
But why is it that when I run python scripts, the datetime in python is different than datetime in mysql? –  Alex Mar 31 '10 at 7:43
    
Please provide examples. –  Insyte Mar 31 '10 at 15:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.