On server, su to root and do this:
# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql
(Note that the command at the end is of course mysql
, and, you're sending it to a table which happens to have the same name: mysql
.)
Next, you can now # ls /usr/share/zoneinfo
.
Use that command to see all the time zone info on ubuntu or almost any unixish server.
(BTW that's the convenient way to find the exact official name of some time zone.)
For example
mysql> select ts, CONVERT_TZ(ts, 'UTC', 'Pacific/Tahiti') from example_table ;
+---------------------+-----------------------------------------+
| ts | CONVERT_TZ(ts, 'UTC', 'Pacific/Tahiti') |
+---------------------+-----------------------------------------+
| 2020-10-20 16:59:57 | 2020-10-20 06:59:57 |
| 2020-10-20 17:02:59 | 2020-10-20 07:02:59 |
| 2020-10-20 17:30:08 | 2020-10-20 07:30:08 |
| 2020-10-20 18:36:29 | 2020-10-20 08:36:29 |
| 2020-10-20 18:37:20 | 2020-10-20 08:37:20 |
| 2020-10-20 18:37:20 | 2020-10-20 08:37:20 |
| 2020-10-20 19:00:18 | 2020-10-20 09:00:18 |
+---------------------+-----------------------------------------+