Most of us are familiar with how MySQL handles timezones, and what one must do to keep up with the latest changes, etc. Most of us use the system timezone info, which is great – one less item to update when changes occur (usually because of daylight savings changes). And for those who need more specifics, most everyone (except Windows and HP-UX) can update their timezone info using the zoneinfo database from the OS.
For those few Windows and HP-UX, you have to download the package of pre-built time zone tables that MySQL provides. Per the manual (timezone section):
“If your system is one that has no zoneinfo database (for example, Windows or HP-UX), you can use the package of pre-built time zone tables that is available for download at the MySQL Developer Zone:”
So the questions becomes, is that your only option on Windows?
The answer is “no”.
At the top of the same manual page, it says:
“You can also set it by setting the TZ environment variable before you start mysqld. The permissible values for –timezone or TZ are system dependent. Consult your operating system documentation to see what values are acceptable.”
Now, it doesn’t tell you what these are since they are OS-dependant. Also, I couldn’t easily find any mentions of what the proper settings for Windows are for MySQL, but in the end, and after a good bit of testing various values (especially around DST times), I found what works (at least in the U.S. for now).
Of course, first, you must create an “environment variable” named TZ, unless you already have one, but that is doubtful (at least it is not default in my experience). If you are not familiar with how to create an environment variable, perhaps you should not do this for your first attempt, as I don’t want you to mess up your system. But for those who would still like to know:
Control Panel -> System -> Advanced System Settings -> Environment Variables -> Click "New" *under* "System variables"
You’ll want to give the “System variable” the name: TZ
And then ‘some’ of the permissible values are:
EST5EDT CST6CDT MST7MDT PST8PDT
I had only tested these in the U.S., so you’ll have to perform further tests if you wish to use other values. If you do use others, be sure to test them thoroughly, especially around your respective DST changeovers.
Fwiw, I did post the above tip in the “User Comments” on the manual page, but wanted to share the info here, in case to make it a bit easier to find.
Now why would any of this be useful?
Well, no values for TZ on Windows have been documented, nor tested, so at least this helps with that.
And as for practical uses, this would allow your MySQL to use the “system”, but it could still differ from the “system”. That is, your server could be in Pacific time zone, and you want the server system to remain PST, but you want MySQL to be Eastern (EST) for whatever reason. This would allow you to accomplish that without having to load the MySQL timezone tables (and then keep them up-to-date when changes occur).
Note that if you do set TZ to “EST5EDT”, after re-starting MySQL, you will see (it only shows “EDT”):
mysql> show global variables like '%zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | EDT | | time_zone | SYSTEM | +------------------+--------+
This is normal, and expected, and the same occurs for EST5EDT, CST5CDT, MST5MDT, and PST5PDT.
Hope this helps. 🙂