MySQL Bugs: #66678: convert_tz inconsistency -- convert

Bug #66678	convert_tz inconsistency -- convert_tz returns null when it should work
Submitted:	4 Sep 2012 11:29	Modified:	5 Sep 2012 20:23
Reporter:	matteo sisti sette	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.5.16, 5.5.28	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	datetime convert_tz

Description:
Time conversion from @@global.time_zone to a given named timezone works fine. 
Time conversion from the very same global system time zone named explicitly and the very same given named timezone fails and returns null.

How to repeat:

SELECT 
 @@global.time_zone, 
 @@global.system_time_zone, 
 CONVERT_TZ(NOW(),@@global.time_zone, 'US/Central'),  
 CONVERT_TZ(NOW(),'CEST', 'US/Central');

The absurd result:

SYSTEM	CEST	2012-09-04 06:27:37	NULL

The first two columns in the result show that the current global timezone is "CEST". The third column shows that converting from the global timezone (which is CEST) to US/Central works fine. However, converting from CEST to US/Central fails, which is ridiculous.

On both MySQL 5.1.63 and 5.5.23 (Windows 7, Danish) I get the result:

SYSTEM
Rom, sommertid
(NULL)
(NULL) 

.. with the modified query:

SELECT 
 @@global.time_zone, 
 @@global.system_time_zone, 
 CONVERT_TZ(NOW(),@@global.time_zone, 'US/Central'),  
 CONVERT_TZ(NOW(),'Rom, sommertid', 'US/Central');

Please ignore my post.  I don't have data in the timzone tables!
(http://dev.mysql.com/downloads/timezones.html)

Actually, though I do have time zone tables, in my time_zone_name table there's no entry with name 'CEST'.
But then, the question is either why the third column of the result of my query is not null, or why @@global.system_time_zone returns CEST.

There's an inconsistency in that convert_tz can convert from and to SYSTEM correctly, yet @global.system_time_zone returns a time zone name that is not in the time zone tables.

Also see this
http://bugs.mysql.com/bug.php?id=66681

I think the conclusion is that if not timezone names are imported from the system (what I really don't know how to do on Windows) the timezone names available is not 'covering' for names used by today's systems.

But let MySQL/Oracle people comment.

Thank you for the report.

You run query:

> SELECT 
 @@global.time_zone, 
 @@global.system_time_zone, 
 CONVERT_TZ(NOW(),@@global.time_zone, 'US/Central'),  
 CONVERT_TZ(NOW(),'CEST', 'US/Central');

Ang get result:

> SYSTEM	CEST	2012-09-04 06:27:37	NULL

But @@global.time_zone is SYSTEM, not CEST. Converting from SYSTEM works just fine:

mysql> select CONVERT_TZ(NOW(),'SYSTEM', 'US/Central');
+------------------------------------------+
| CONVERT_TZ(NOW(),'SYSTEM', 'US/Central') |
+------------------------------------------+
| 2012-09-05 14:14:01                      |
+------------------------------------------+
1 row in set (0.00 sec)

Regarding to CEST try to use name from mysql.time_zone_name table: right/US/Central

That's precisely the inconsistency.

Yes, global.time_zone is SYSTEM, and converting from SYSTEM works fine. 

But what does "SYSTEM" mean? It means "the same as global.system_time_zone" (or that's what the documentation says). And global.system_time_zone is CEST.

So if system_time_zone returns CEST, and converting from SYSTEM works just fine, but converting from CEST doesn't, something is wrong.

I know there is certainly some other name in the time zone table equivalent to CEST. Then the issue is that SELECT @@global.system_time_zone should return that value.

Consider the following real life example (just to show why this is important).
Suppose we receive messages which may come from different places. We want (for whatever reason) to save each message with the date and time of the place it came from, BUT we want to keep in a separate field the information of which timezone the time is relative to. If we receive a message whose origin we can't determine (or that we know to be "local"), we want to save it with the system datetime (just NOW() without any conversion) but we also want to store the information of which time zone it was saved relative to. We can't save it as "SYSTEM", because if some day we move the server to another country and change its timezone, we won't know what "SYSTEM" was at the time of saving.
So we read @@global.system_time_zone and save that value.

Only that this value is not a valid value that can be used with convert_tz, because of this bug.

Thank you for the feedback.

But this is how timezones are stored in OS zoneinfo directory. Actually CET (winter time) exists.

But I agree users can be confused, so verifying as documentation bug.

Similar bug #66681 marked as duplicate of this one.

I'm afraid it's a little more than a documentation issue.

A way should be provided to query the server to ask "Tell me what the current system time zone is, expressed in a way which I can pass to CONVERT_TZ (but which does contain the information, unlike "SYSTEM"), if it exists"

I agree with the bug poster, this isn't a documentation issue. Or at least it's not clear what the docs should say to address this lack of time zone capability. Recategorizing to server bug for further analysis.

Any update on this bug?