Link to home
Create AccountLog in
Avatar of pmsguy
pmsguy

asked on

How do I set the default timezone for the database for mysql?

I want to set the default timezone for each database I have.

I read I can use --default-time-zone='timezone', see http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_time_zone

I read through http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html which says I can issue a mysql to set the session.

SET time_zone=timezone;

However, I want to define the default for each database WITHOUT issuing the mysql command
"SET time_zone=timezone;"

The goal is to have my database boot up with this setting.
So if I use php or the command line mysql I do NOT have to run the command
SET time_zone=timezone;

The problem is if I do an import  "mysql < import.sql" WITHOUT issues a timezone command the mysql process will set date/times relative to the system and NOT the database timezone I want.

In conclusion, is there some meta data for the database to set the timezone?
SELECT @@global.time_zone, @@session.time_zone;

OR is there some tuning I can do for each database to automatically set the timezone for the database connection WITHOUT issue the command SET time_zone=timezone;
SOLUTION
Avatar of cyberkiwi
cyberkiwi
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You might consider setting the timezone to UTC.  The value of PHP time() is always the same, around the world, and it is relative to UTC.  You can store all your DATETIME values in the form of Unix timestamps.

Then you can use date_default_timezone_set() in PHP to set the relative time for your date/time functions and outputs.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account