Bug #19945 no DATE_FORMAT(date, %Z)
Submitted: 19 May 2006 16:15 Modified: 21 May 2006 15:13
Reporter: Paul Miller Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:5.0.19 OS:Any
Assigned to: CPU Architecture:Any

[19 May 2006 16:15] Paul Miller 
Description:
no easy way to get the timezone from a date field
would like a 
   %Z     alphabetic time zone abbreviation (e.g., EDT)

currently using

mysql> select time_zone_transition_type.Abbreviation from time_zone_transition_type, time_zone_name where time_zone_name.Name='US/Eastern' and time_zone_name.Time_zone_id=time_zone_transition_type.Time_zone_id and time_zone_transition_type.Transition_type_id=(select time_zone_transition.Transition_type_id from time_zone_transition, time_zone_name where time_zone_name.Name='US/Eastern' and time_zone_name.Time_zone_id=time_zone_transition.Time_zone_id and time_zone_transition.Transition_time<UNIX_TIMESTAMP(CONVERT_TZ('2006-04-02 03:00:00', 'US/Eastern', 'UTC')) order by time_zone_transition.Transition_time desc limit 1);
+--------------+
| Abbreviation |
+--------------+
| EST          |
+--------------+
1 row in set (0.00 sec)

mysql> select time_zone_transition_type.Abbreviation from time_zone_transition_type, time_zone_name where time_zone_name.Name='US/Eastern' and time_zone_name.Time_zone_id=time_zone_transition_type.Time_zone_id and time_zone_transition_type.Transition_type_id=(select time_zone_transition.Transition_type_id from time_zone_transition, time_zone_name where time_zone_name.Name='US/Eastern' and time_zone_name.Time_zone_id=time_zone_transition.Time_zone_id and time_zone_transition.Transition_time<UNIX_TIMESTAMP(CONVERT_TZ('2006-04-02 03:00:01', 'US/Eastern', 'UTC')) order by time_zone_transition.Transition_time desc limit 1);
+--------------+
| Abbreviation |
+--------------+
| EDT          |
+--------------+

How to repeat:
feature request