Bug #2116 Timezones are hard to handle in mysql
Submitted: 15 Dec 2003 1:27 Modified: 30 Jul 2004 18:41
Reporter: Rasmus Tengblad Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S4 (Feature request)
Version:All? OS:Any (All)
Assigned to: Dmitry Lenev CPU Architecture:Any

[15 Dec 2003 1:27] Rasmus Tengblad
The timezone used in date-and-time conversions is the timezone that the server was started in, and when connecting to a mysql server there is no way (AFAIK) for the client to figure out in what timezone the server is running.

How to repeat:
su-2.02# TZ=NZ daemon /usr/local/bin/safe_mysqld --skip-networking
Starting mysqld daemon with databases from /var/mysqldata
su-2.02# date
Mon Dec 15 10:21:49 CET 2003
su-2.02# mysql -e 'select now()'
| now()               |
| 2003-12-15 22:22:18 |

Suggested fix:
I think the best way to solve this would be to make the UNIX_TIMESTAMP()-function able to parse timezones as well, e.g: SELECT UNIX_TIMESTAMP('2003-12-12 07:15:00 EST');

For completeness, there should of course also be a %-conversion in the functions FROM_UNIXTIME() and DATE_FORMAT() that result in the name of the timezone in use. A third optional argument allowing the user to specify which timezone to use, would also be great.
[15 Dec 2003 11:35] Alexander Keremidarski
While I agree with Synopsis you are wrong sayinng that "there is no
way (AFAIK) for the client to figure out in what timezone the server is

Here is the way:

mysql> SHOW VARIABLES LIKE "timezone";
| Variable_name | Value |
| timezone      | EET   |

But in general MySQL doesn't matches ANSI standard in this respect so we have standard compliant TZ handling in our TODO. 

For example there is no easy way to use timezone system variable in queries or convert between different zones, not even way to convert timezone name to hour difference.

That's why I am setting status to Deffered. This is something to be implemented in some of future releases.
[15 Dec 2003 13:20] Sergei Golubchik
actually this is something that is being implemented now and is planned to appear in 5.0
[30 Jul 2004 18:41] Sergei Golubchik
Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 

Additional info:

implemented in 4.1.3