Skip navigation links
B.5.5 Query-Related Issues »
Section Navigation      [Toggle]

B.5.4.6. Time Zone Problems

If you have a problem with SELECT NOW() returning values in UTC and not your local time, you have to tell the server your current time zone. The same applies if UNIX_TIMESTAMP() returns the wrong value. This should be done for the environment in which the server runs; for example, in mysqld_safe or mysql.server. See Section 2.20, “Environment Variables”.

You can set the time zone for the server with the --timezone=timezone_name option to mysqld_safe. You can also set it by setting the TZ environment variable before you start mysqld.

The allowable values for --timezone or TZ are system-dependent. Consult your operating system documentation to see what values are acceptable.


User Comments

Posted by Rusty Carruth on December 18 2002 3:27pm[Delete] [Edit]

Let me try to help clarify with a howto:

First, set things up:

log in as root.
stop mysql (/etc/rc.d/init.d/mysql stop)
start mysql in ignore mode (/usr/bin/safe_mysqld
--skip-grant-tables &)

now, change the password for root user:

mysql -p
<enter root password? if requested, or don't use
the -p option>
use mysql;
update user set password=password('newpass');
quit
/etc/rc.d/init.d/mysql stop # Stop the insecure
server
/etc/rc.d/init.d/mysql start # restart it in
normal mode

Ok,now root's password is 'newpass'. Now, to
change password for user 'joe_user', do this as
root:

mysqladmin -u joe_user password newpass -p
(enter root password - newpass in this case)

Repeat for all users (bug please don't use
'newpass' as the password!).

I wish I had found a way to do this without having
to type the new password on the command line, but
I could not. Hopefully someone else will figure
it out and comment here...

Hope this saves someone the time *I* spent on
this!

rc

Posted by Richard Lloyd on May 17 2002 2:24am[Delete] [Edit]

I just downloaded the Linux 3.23.49a RPMs and the
default location for the creation of mysql.sock is
NOT /tmp !! It's actually in /var/lib/mysql. This is
quite serious because not only doesn't this match
all the online docs and mysqld man page, it also
causes a problem if MySQL isn't running when you
configure PHP because the latter defaults to
/tmp/mysql.sock and if you then try to connect
locally to mysql via PHP, it'll fail.

Posted by Brent Butler on December 12 2003 6:34am[Delete] [Edit]

I was having trouble getting MySQL to recognize my Unix/FreeBSD server's timezone. I kept reading on this site that I needed to set the TZ environmental variable.

After a couple of hours of frustration trying to find out how you define an environmental variable (which I'm no closer to solving than I was before all this started), I discovered that upon restart, MySQL seemed to be getting the timezone information from ~/etc/localtime on the server.

I overwrote that localtime file with the one for my timezone as provided by my servers' host, restarted MySQL, and had no problems; MySQL picked up my time zone perfectly.

Just for gits and shiggles, I tried a few other time zones (PST/PDT, MST/MDT, CST/CDT, EST/EDT, Fiji's, Kabul's, Baghdad's, Tokyo's, Honolulu's, UTC, GMT, etc). Just as what happened with my local time zone (which is CST/CDT), MySQL recognized and used the new time zone.

Hopefully this will help someone in a similar situation as mine in the future!

Posted by Kamal Siddiqi on April 10 2004 12:55am[Delete] [Edit]

Thats smart Brent Butler I have the same problem I want to change the time zone of mysql (ver. 4.1.2), but when I opened the file /etc/localtime, it has following mess:

TZif?V^G?ðW%·?WçuðY^NÔ^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^B^C^Bÿÿ£^L

Please tell me what should I modify it to, to change it to the time zone of Pakistan, thank you.

Posted by Brent Butler on January 3 2005 7:52pm[Delete] [Edit]

Kamal Siddiqi--

I haven't the slightest clue what information is contained within these files, but on my particular server, they were all located in this directory:
~/usr/share/zoneinfo

Upon exploring that directory, I found files for all major timezones within the United States and a slew of others, including Karachi, Pakistan. I believe they are all a function of FreeBSD, but I'm honestly not sure.

Since I only barely have a grasp as to how all this functions, I would recommend "Googling" your way to your solution. To see some of these time zones in action, take a visit to http://www.brentbutler.com/timezones.html

Posted by Jean-Serge Gagnon on February 1 2005 7:14pm[Delete] [Edit]

On RedHat systems, the /etc/localtime file is normally a link to your time zone file in /usr/share/zoneinfo.

You should use dateconfig or redhat-config-time to change since other files may need to be updated (like /etc/sysconfig/time), but with regards to the localtime file, it's as simple as:

rm -f /etc/localtime
ln -s /usr/share/zoneinfo/America/New_York /etc/localtime

BTW, these are binary files, not text.

Posted by Chad MILLER on March 8 2006 2:31pm[Delete] [Edit]

Note: The contents of the timezone file is not human-editable. It's a description to the computer how it should behave for timezones, with leap years Daylight Saving Time, etc etc.

One never edits those files. One does occasionally change the symlink to point to a different timezone-data file (or copy it, or hard-link it).

Posted by Lee Wood on July 4 2008 4:38pm[Delete] [Edit]

For the benefit of anyone wanting to set the timezone to UTC, on a Macintosh, you can go through the symbolic link method:

$ sudo bash
$ rm -f /etc/localtime
$ ln -s /usr/share/zoneinfo/UCT /etc/localtime

, or you can use the "System Preferences"/"Date & Time"/"Time Zone" panel, and set the 'Nearest City' to UTC.

Add your own comment.