1

I have spent the past hour trying different Google searches and I can't find any detailed description of the MYSQL timezone tables. I have looked at them, but that is not enough for me to figure them out (at least yet).

I don't mind reading the manual, but can anyone point me to one that describes the structure of these tables?

Then I want to generate SQL that can take a Timezone name and produce a timezone abbreviation. And then the reverse that goes from abbreviation and country code to timezone name. I have found some suggested SQL that does some of this, but I am hesitant to use those without some understanding of the tables they access.

/*
Input:  sourcedt : Input datetime string in context of source
            sourcetzn : timezone used for sourcedt
            basetzn:  Default timezone used by server
            displaytz:  Timezone for displaying date
Output:
        @displaydt :  Date time in context of source
        is_DST      : set 1 when output is in daylight savings time
        Abbreviation: Timezone abbreviation
        Offset:     GMT offset of displaytzn
*/
SET @sourcetzn = 'America/New_York';
SET @basetzn = 'America/Los_Angeles';
SET @displaytzn = 'America/Chicago';
SET @sourcedt = '2015-07-25 21:33:00';
SET @basedt = CONVERT_TZ(@sourcedt,@sourcetzn,@basetzn);
SET @displaydt = CONVERT_TZ(@basedt,@basetzn, @displaytzn);
SET @utime = unix_timestamp(@basedt);

SELECT
@displaydt,ztt.Abbreviation,ztt.Is_DST,ztt.`Offset`
FROM  mysql.`time_zone_name` tzn
JOIN    mysql.time_zone_transition_type ztt ON ztt.Time_zone_id=tzn.Time_zone_id
INNER JOIN mysql.time_zone_transition tzt
ON tzt.Time_zone_id = ztt.Time_zone_id
AND tzt.Transition_type_id = ztt.Transition_type_id
where tzn.Name = @displaytzn
and  @utime >   tzt.Transition_time 
order by Transition_time desc
LIMIT 1;
Jim Mc
  • 336
  • 1
  • 10
  • Have you added the mentioned tables to your MySQL instance? They're MyISAM so all you have to do is copy them into your data dir on Windows, they should appear under the database where you added the tables. Then you can use simple `SHOW CREATE TABLE tablename;` to see the structure, or other tools you usually use to check what the table consists of. – N.B. Nov 09 '15 at 21:37
  • @N.B. Nobody mentioned Windows... – Mike Nov 09 '15 at 22:02
  • Possible duplicate of [MySQL Time Zones](http://stackoverflow.com/questions/9808160/mysql-time-zones) – Mike Nov 09 '15 at 22:02
  • Yes. I have added the tables so functions like CONVER_TZ work fine. And I can see the table structures, but have not figured out what the values mean. I will post a test query I have been using based on a suggestion which I can't find right now that sort of works, but does not seem to handle daylight savings time. – Jim Mc Nov 09 '15 at 22:04
  • @Mike - does anyone have to mention Windows? It's Windows that are missing time zone info so MySQL has to "support" it by creating these tables. Linux is already prepared to handle this scenario. Some people.. – N.B. Nov 09 '15 at 22:05
  • @N.B. Actually linux has the same problem. If the tables aren't loaded (which was the default for me on Debian), so you're limited to number offsets (e.g. +5, -6, etc). Trying to use named timezones will fail. – Mike Nov 09 '15 at 22:08
  • These comments remind me of my Google search; No talk about the structure of the tables, just that you need them and how to load them. Well I did that already. Thanks for your input. – Jim Mc Nov 09 '15 at 22:10
  • There's a lot to be said in favour of ditching timezones in mySQL completely, storing everything in UTC, and converting at input/output time. But then I don't know what you want to do – Pekka Nov 09 '15 at 22:11
  • 1
    Thanks Pekka. We considered that. One still needs to solve this problem for display purposes. For DATETIME's you need to have an application level convention of what timezone is implied. Using UTC is one such convention that can work. We chose, instead, to store the DATETIME as presented to us and to also store the time zone context in a paired VARCHAR field. This preserves the input without converting it . – Jim Mc Nov 09 '15 at 22:23
  • 1
    The `timestamp` approach that @Pekka웃 suggested is the way this issue is tackled. You store in UTC, you retrieve in UTC and you deliver in UTC. If you have to perform time zone conversions for any reasons, you do so when needed. That saves you so much hassle you're not even aware of. The issue doesn't lie only in pulling the data out and formatting it, what about querying the data using date ranges? As for display purposes - you pull the data, you have users' time zone info and you simply convert from UTC > desired time zone at the point of output. No time zone supplied, no conversion. – N.B. Nov 09 '15 at 22:27
  • There are indeed cases where time zone conversion is needed at the database layer, such as when attaching to a database from a reporting system, or connecting from an application such as Excel. But in the vast majority of use cases where a custom application is involved, it's better to work with time zones in the application layer only. Storing local datetime + time zone is valid in *scheduling* applications (recurrence rules, etc), but not for timestamping. Some context would help here. – Matt Johnson-Pint Nov 10 '15 at 01:45
  • Regarding abbreviations - be very careful. There are ambiguities and they are not consistent. Also there's not always an abbreviation for the generic form of a time zone id - you have to use a specific date and time. Example, `America/Los_Angeles` has `PT`, `PST` & `PDT`, but `Europe/London` has only `GMT` and `BST`. There is no generic abbreviation. – Matt Johnson-Pint Nov 10 '15 at 01:49

0 Answers0