Database Timezone Configuration

Reference

  • https://craftcms.com/knowledge-base/populating-mysql-mariadb-timezone-tables
  • https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-installation

Confirm timezones are configured

Craft will flag this as a log message or in the System Report page in the Utilities section in the Control Panel.

Alternatively, you can confirm directly on the server.

SSH onto the relevant server and run the following command:

mysql -uforge -p -e "SELECT COUNT(*) FROM mysql.time_zone_name"

Enter the password for the forge user. This should be stored in Bitwarden.

The output should look something like this if timezones are not configured:

forge@ktp-prod-next:~$ mysql -uforge -p -e "SELECT COUNT(*) FROM mysql.time_zone_name"
Enter password: 
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

If they are correctly configured, the output should look something like this:

forge@ktp-prod-next:~$ mysql -uforge -p -e "SELECT COUNT(*) FROM mysql.time_zone_name"
Enter password: 
+----------+
| COUNT(*) |
+----------+
|      499 |
+----------+

Configuring timezones for MySQL

SSH onto the relevant server and run the following command:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uforge -p mysql

When prompted, enter the password for the forge user. This should be stored in Bitwarden.

The output should look something like this:

forge@<server>:~$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u forge -p mysql
Enter password: 
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zonenow.tab' as time zone. Skipping it.

It's safe to ignore the warnings, but still a good idea to confirm that timezone support is configured correctly but running the SQL mentioned above.