Save date in America Pacific timezone format in MySQL

Question

I'm trying to save date in the America Pacific timezone format. I execute before querying the following query

SET time_zone='-09:00'

but when I close the connection and reopen it then the date seems in the local format. Do you know why?

Answer 1

Per MySQL Documentation on Time Zone Support

SET time_zone = timezone; is a per connection setting and so when connection resets, it's resets to default value.

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;

If you want to set it globally then you can either

Change the configuration file (.cnf file) to reflect the change

(OR)

If you have SUPER privilege then you can set the global server time zone value at runtime with the command below

SET GLOBAL time_zone = timezone;

Answer 2

You'll need to edit your my.cnf and reboot to set it permanently.

essentially you'll want to add this line to /etc/my.cnf

default_time_zone='-09:00'

See these articles for more details.

How do I set the time zone of MySQL?

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_time_zone