Setting the MySQL time zone for your website

The database server on shared hosting runs in UTC for everyone, so NOW() and automatic timestamps may be an hour or more different from your local time. For example, Nigeria (West Africa Time) is UTC+1.

You can’t change the server’s own time zone on shared hosting, but you can set the time zone for your website’s connection, which is all your website needs.

Check the current time zone

In phpMyAdmin, open the SQL tab and run:

SELECT NOW(), @@global.time_zone, @@session.time_zone;

Set it for one connection

Run this straight after your app connects to the database:

SET time_zone = '+01:00';

Use an offset such as +01:00 (Lagos), +00:00 (London in winter) or -05:00 (New York in winter). Named time zones such as Africa/Lagos may not be available, and offsets always work.

Offsets don’t change for daylight saving time. If your location has daylight saving, it’s usually better to store dates in UTC and convert them in your app, as described below.

In PHP

Set both PHP’s and MySQL’s time zone, so they match:

date_default_timezone_set('Africa/Lagos');

// PDO
$pdo = new PDO('mysql:host=localhost;dbname=your_database;charset=utf8mb4', 'db_user', 'db_password');
$pdo->exec("SET time_zone = '+01:00'");

// mysqli
$mysqli = new mysqli('localhost', 'db_user', 'db_password', 'your_database');
$mysqli->query("SET time_zone = '+01:00'");

You can also set PHP’s time zone for the whole account with date.timezone in Select PHP Version → Options. See Changing PHP versions and settings.

In WordPress

WordPress handles this for you. Go to Settings → General → Timezone and choose a city, such as Lagos. WordPress stores times in UTC and shows them in your time zone.

In Laravel and other frameworks

Set the time zone in the framework’s database settings. In Laravel, add 'timezone' => '+01:00' to the mysql connection in config/database.php, and set APP_TIMEZONE (or 'timezone' in config/app.php).

Best practice

Store dates in UTC and convert them to the visitor’s time zone when you display them. This avoids problems with daylight saving time and visitors in other countries.

Related articles

• Changing the time zone in the Linux shell
• Database limits and versions