MySQL convert_tz 返回 null

convert_tz returns null

提问by mohur

I know this sounds stupid, but when I use

我知道这听起来很愚蠢，但是当我使用

SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','Asia/Jakarta') AS time

it outputs NULL. I'm using MySQL Workbench in Ubuntu 12.04 64 bit, and it works in my other laptop/os (also using MySQL Workbench).

它输出NULL。我在 64 位 Ubuntu 12.04 中使用 MySQL Workbench，它可以在我的其他笔记本电脑/操作系统中使用（也使用 MySQL Workbench）。

回答by Barmar

This will happen if you haven't loaded the time zone table into mysql.

如果您尚未将时区表加载到 mysql 中，就会发生这种情况。

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

回答by Jim Schubert

I found this thread after spending some time trying to figure out why after running the command in the accepted answer (which is the same on MySQL's dev site) the command was unable to convert between timezones such as

我花了一些时间试图弄清楚为什么在接受的答案（在 MySQL 的开发站点上相同）中运行命令后，该命令无法在时区之间进行转换，例如

SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','MET') AS time

It turns out that on OS X there are two files that cause problems: /usr/share/zoneinfo/Factoryand /usr/share/zoneinfo/+VERSION.

事实证明，在 OS X 上有两个文件会导致问题：/usr/share/zoneinfo/Factory和/usr/share/zoneinfo/+VERSION.

The fix... temporarily moving these files to a different location such as /usr/share/zoneinfo/.bak/allows for the command

修复...暂时将这些文件移动到不同的位置，例如/usr/share/zoneinfo/.bak/允许命令

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

to fully populate all of the expected timezone information.

完全填充所有预期的时区信息。

This may or may not be a bug in my installed version of MySQL:

这可能是也可能不是我安装的 MySQL 版本中的错误：

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.11, for osx10.6 (x86_64) using  EditLine wrapper

I am also operating in STRICT_MODE.

我也在STRICT_MODE.

In any case, I hope this saves a few headaches for anyone searching for the fix.

无论如何，我希望这可以为任何寻找修复程序的人省去一些麻烦。

回答by Rakesh

Apart from Windows environment, You can set Time Zone by

除了 Windows 环境，您还可以通过以下方式设置时区

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

In Windowsenvironment,

在Windows环境中，

1. download Time zone description tables fromhttp://dev.mysql.com/downloads/timezones.html

1. download Time zone description tables fromhttp://dev.mysql.com/downloads/timezones.html

2. Stop MySQL server

2. Stop MySQL server

3. Put then inside Mysql installation package(ie. C:\Program Files\MySQL\data\mysql)`

3. Put then inside Mysql installation package（即 C:\Program Files\MySQL\data\mysql）`

4. Start MySQL server

4. Start MySQL server

..Your work is finished..

..你的工作完成了..

If still you are getting NULLfor CONVERT_TZDownload these database tables and insert it into mysql database http://www.4shared.com/folder/Toba2qu-/Mysql_timezone.html

如果您仍然NULL需要CONVERT_TZ下载这些数据库表并将其插入 mysql 数据库http://www.4shared.com/folder/Toba2qu-/Mysql_timezone.html

Now you problem will be solved.. :)

现在你的问题将得到解决.. :)

回答by Domenic D.

If you are using MySql on Windows you have to load the timezone data into the mysql schema. Here is a good HOWTO: http://www.geeksengine.com/article/populate-time-zone-data-for-mysql.html

如果您在 Windows 上使用 MySql，则必须将时区数据加载到 mysql 架构中。这是一个很好的 HOWTO：http: //www.geeksengine.com/article/populate-time-zone-data-for-mysql.html

If you don't do this, the function CONVERT_TZ won't recognize your input timezone (i.e. your examples: 'UTC','Asia/Jakarta'), and will simply return NULL.

如果您不这样做，函数 CONVERT_TZ 将无法识别您的输入时区（即您的示例：'UTC'、'Asia/Jakarta'），并且只会返回 NULL。

回答by shanu

1) In Windows, there isn't any data folder now in C:\Program Files\MySQL\as in other answers.

1) 在 Windows 中，现在没有任何数据文件夹，C:\Program Files\MySQL\就像其他答案一样。

2) In that case, look for C:\ProgramData\MySQL\MySQL Server 5.x\Data\mysql. Generally this folder hidden and you will not see C:\ProgramData\some times.

2) 在这种情况下，查找C:\ProgramData\MySQL\MySQL Server 5.x\Data\mysql. 通常这个文件夹是隐藏的，你C:\ProgramData\有时不会看到。

3) Change the Settings in View tab to see Hidden files and Folders as explained here https://irch.info/index.php?pg=kb.page&id=133

3) 更改视图选项卡中的设置以查看隐藏文件和文件夹，如下所述https://irch.info/index.php?pg=kb.page&id=133

4) Stop the MySQL service by searching for "services" in Windows Start button.

4) 通过在 Windows 开始按钮中搜索“服务”来停止 MySQL 服务。

5) Then unzip the timezone_2017c_posix.zip and then copy the files in it (copy the files directly, don't copy the whole folder itself), and paste in C:\ProgramData\MySQL\MySQLServer5.x\Data\mysql\

5）然后解压timezone_2017c_posix.zip，然后复制里面的文件（直接复制文件，不要复制整个文件夹本身），粘贴进去 C:\ProgramData\MySQL\MySQLServer5.x\Data\mysql\

6) For MySQL 5.7, timezone_2017c_posix.zip will just give a .sql file after unzipping and it may not solve the issue. So go ahead and download the zip file for 5.6 even if you are running MySQL 5.7 and copy those files to C:\ProgramData\MySQL\MySQL Server 5.x\Data\mysql\

6) 对于 MySQL 5.7，timezone_2017c_posix.zip 解压后只会给出一个 .sql 文件，可能无法解决问题。因此，即使您正在运行 MySQL 5.7，也请继续下载 5.6 的 zip 文件并将这些文件复制到C:\ProgramData\MySQL\MySQL Server 5.x\Data\mysql\

7) Restart the MySQL server. To check if the CONVERT_TZ () is working, run this sql query.

7) 重启 MySQL 服务器。要检查 CONVERT_TZ () 是否正常工作，请运行此 sql 查询。

SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','Asia/Jakarta');and check for non-null output.

SELECT CONVERT_TZ('2004-01-01 12:00:00','UTC','Asia/Jakarta');并检查非空输出。

回答by Timo Huovinen

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

if you get the error data too long for column 'abbreviation' at row 1then see: https://bugs.mysql.com/bug.php?id=68861

如果您收到错误，data too long for column 'abbreviation' at row 1请参阅：https: //bugs.mysql.com/bug.php?id=68861

the fix would be to run the following

解决方法是运行以下命令

this will add a line to disable the mysql mode and allow mysql to insert truncated data this was because of a mysql bug where mysql would add a null character at the end (according to the above link)

这将添加一行以禁用 mysql 模式并允许 mysql 插入截断的数据这是因为 mysql 错误，其中 mysql 会在末尾添加一个空字符（根据上面的链接）

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
(if the above gives error "data too long for column 'abbreviation' at row 1")
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/zut.sql

echo "SET SESSION SQL_MODE = '';" > /tmp/mysql_tzinfo_to.sql
cat /tmp/zut.sql >> /tmp/mysql_tzinfo_to.sql

mysql --defaults-file=/etc/mysql/my.cnf --user=verifiedscratch -p mysql < /tmp/mysql_tzinfo_to.sql

回答by Jaseem Abbas

MAMP PRO

MAMP专业版

1. Open Terminal
2. cd /usr/share/zoneinfo/
3. sudo mv +VERSION ~/Desktop
4. cd /applications/MAMP/Library/bin
5. sudo ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -p -u root mysql
6. sudo mv ~/Desktop/+VERSION /usr/share/zoneinfo/

1. 打开 Terminal
2. cd /usr/share/zoneinfo/
3. sudo mv +VERSION ~/Desktop
4. cd /applications/MAMP/Library/bin
5. sudo ./mysql_tzinfo_to_sql /usr/share/zoneinfo | ./mysql -p -u root mysql
6. sudo mv ~/Desktop/+VERSION /usr/share/zoneinfo/

回答by Stranger

These are the steps to make it work if you're in windows and using MySQL 5.7.

如果您在 Windows 中使用 MySQL 5.7，这些是使其工作的步骤。

1. Right click on My Computer/Computer/This PC or whatever the name in your OS and choose Properties.
2. Choose "Advanced system settings" from the left panel.
3. Choose "Environmental Variables", enter the complete path name of your MySQL bin directory (generally it will be in, C:\Program Files\MySQL\MySQL Server 5.7\bin).
4. Open cmd prompt, enter into mysql using mysql -u root -p password.
5. Enter use mysqlto select the MySQL DB.
6. Download the file "timezone_YYYYc_posix_sql.zip" (In the place of YYYY, substitute the maximum year available in that page like 2017 or 2018) from https://dev.mysql.com/downloads/timezones.html.
7. Extract it and open the file in text editor.
8. Copy the contents and execute in the cmd prompt.

1. 右键单击我的电脑/计算机/这台电脑或操作系统中的任何名称，然后选择属性。
2. 从左侧面板中选择“高级系统设置”。
3. 选择“Environmental Variables”，输入你的MySQL bin目录的完整路径名（一般在C:\Program Files\MySQL\MySQL Server 5.7\bin）。
4. 打开 cmd 提示符，使用mysql -u root -p password.
5. 输入use mysql以选择 MySQL 数据库。
6. 从https://dev.mysql.com/downloads/timezones.html下载文件“timezone_YYYYc_posix_sql.zip”（代替 YYYY，替换该页面中可用的最大年份，如 2017 或 2018）。
7. 提取它并在文本编辑器中打开文件。
8. 复制内容并在cmd提示符下执行。

On successful completion, you should be able to use CONVERT_TZand other timezone functions.

成功完成后，您应该能够使用CONVERT_TZ和其他时区功能。

回答by Rem

On Mac OS Catalina when using XAMPP,

在 Mac OS Catalina 上使用 XAMPP 时，

Go to /Applications/XAMPP/xamppfiles/bin folder in Terminal then run following.

转到终端中的 /Applications/XAMPP/xamppfiles/bin 文件夹，然后运行以下命令。

./mysql_tzinfo_to_sql /usr/share/zoneinfo | sed -e "s/Local time zone must be set--see zic manual page/local/" | ./mysql -u root mysql

./mysql_tzinfo_to_sql /usr/share/zoneinfo | sed -e "s/Local time zone must be set--see zic manual page/local/" | ./mysql -u 根mysql

This worked for me.

这对我有用。