Mysql – How to get SQL of single time zone

MySQLtimezone

Trying to get the SQL for a single time zone results in an error message:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo America/Sao_Paulo
mysql_tzinfo_to_sql: Error reading file '/usr/share/zoneinfo' (Errcode: 21)
Problems with zoneinfo file 'America/Sao_Paulo'

What am I doing wrong? Wrong syntax?

Getting the SQL for all time zones works, except for some warnings, and São Paulo is included:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | grep Sao_Paulo
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('America/Sao_Paulo', @time_zone_id);
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('posix/America/Sao_Paulo', @time_zone_id);
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh89' as time zone. Skipping it.
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('right/America/Sao_Paulo', @time_zone_id);
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.

Best Answer

Try this:

$ mysql_tzinfo_to_sql /usr/share/zoneinfo/America/Sao_Paulo America/Sao_Paulo

Here a sample output:

INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('America/Sao_Paulo', @time_zone_id);
INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
 (@time_zone_id, -1767214412, 2)
,(@time_zone_id, -1206957600, 1)
,(@time_zone_id, -1191362400, 2)
,(@time_zone_id, -1175374800, 1)
,(@time_zone_id, -1159826400, 2)
,(@time_zone_id, -633819600, 1)
,(@time_zone_id, -622069200, 2)
,(@time_zone_id, -602283600, 1)
,(@time_zone_id, -591832800, 2)
,(@time_zone_id, -570747600, 1)
,(@time_zone_id, -560210400, 2)
,(@time_zone_id, -539125200, 1)
...
;
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
 (@time_zone_id, 0, -11188, 0, 'LMT')
,(@time_zone_id, 1, -7200, 1, 'BRST')
,(@time_zone_id, 2, -10800, 0, 'BRT')
;

From MySQL documentation

mysql_tzinfo_to_sql tz_file tz_name

This syntax causes mysql_tzinfo_to_sql to load a single time zone file tz_file that corresponds to a time zone name tz_name

Related Question