主页 / dba / 问题 / 31700
Accepted
feklee
Asked: 2013-01-11 07:36:24 +0800 CST

如何获取单个时区的 SQL

  • 772

尝试获取单个时区的 SQL会导致错误消息：

$ 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'

我究竟做错了什么？语法错误？

获取所有时区的 SQL 都有效，除了一些警告，包括圣保罗：

$ 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.
mysql timezone

1 个回答

  1. Best Answer

    尝试这个：

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

    这是一个示例输出：

    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')
;

    来自 MySQL 文档

    mysql_tzinfo_to_sql tz_file tz_name

    此语法导致 mysql_tzinfo_to_sql 加载与时区名称tz_name对应的单个时区文件 tz_file

    • 2

相关问题