0

I have read about how to insert the time zone into MYSQL as below:

I can't find time zone tables in sys database of MySQL

However, after I type show databases, there was no response except showing mysql>

Thus I'd like to clarify how do I go about inserting the timzone-pois.sql into MYSQL.

I am using mysql workbench and mysql 8.0.13 server. Windows 10. Tks.

1

First download the file for 5.7+. Extract the zip file and navigate to unzipped folder from command prompt.

D:\MySQL_FILES>unzip timezone_2018g_posix_sql.zip
Archive:  timezone_2018g_posix_sql.zip
   creating: timezone_2018g_posix_sql/
  inflating: timezone_2018g_posix_sql/timezone_posix.sql

D:\MySQL_FILES>cd timezone_2018g_posix_sql

D:\MySQL_FILES\timezone_2018g_posix_sql>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.13 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
6 rows in set (0.03 sec)

mysql> use mysql;
Database changed
mysql> source timezone_posix.sql

After script completes successfully run some tests.

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2018-12-21 17:07:28 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ(SYSDATE(),'utc','America/Los_Angeles') utc_la;
+---------------------+
| utc_la              |
+---------------------+
| 2018-12-21 09:07:35 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT_TZ(SYSDATE(),'utc','America/New_York') utc_ny;
+------------------------------------------------+
| utc_ny                                         |
+------------------------------------------------+
| 2018-12-21 12:07:41                            |
+------------------------------------------------+
1 row in set (0.00 sec)
0

Looking at the manual, for Windows, you need to download the timezones which is a zip file that contains a SQL file. This can be run in workbench.

  • The problem is I do not know where to port it to MYSQL server. How do I run it ? Insert * from timezone-pois ? – Karen Goh Dec 19 '18 at 1:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.