MySQL Bugs: #2116: Timezones are hard to handle in mysql

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:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	All?	OS:	Any (All)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
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 |
+---------------------+
su-2.02#

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.

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
running."

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.

actually this is something that is being implemented now and is planned to appear in 5.0

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 
http://www.mysql.com/downloads/

Additional info:

implemented in 4.1.3