Import MySQL database without timezone difference - Rocketeers

 On this page

 Knowledge
---------

Import MySQL database without timezone difference
=================================================

### [\#Databases](http://rocketee.rs/index.php/databases)

When you import a MySQL database from a server to your local computer, you might notice that the dates and times are different.

 Published by Mark van Eijk on May 2, 2024 · 1 minute read

1. [Why is that?](#content-why-is-that)
2. [MySQL timezone setting](#content-mysql-timezone-setting)
3. [How can you fix this?](#content-how-can-you-fix-this)

[\#](#content-why-is-that "Permalink")Why is that?
--------------------------------------------------

This is because there is a timezone difference between your computer (probably in your local time) and the server (probably in UTC as it should), while your timestamp columns don't have a timezone specified. As it is your personal computer, you want the time to display in your local timezone. But when developing, you should want everything to be in `UTC` timezone to prevent al sorts of problems (like this one!) and to easily convert and display the correct time(zone) to your users.

[\#](#content-mysql-timezone-setting "Permalink")MySQL timezone setting
-----------------------------------------------------------------------

When you this query in MySQL:

 ```
SELECT @@global.time_zone;

```

You probably get the default value `SYSTEM` returned, this means MySQL uses the timezone your computer is set to.

[\#](#content-how-can-you-fix-this "Permalink")How can you fix this?
--------------------------------------------------------------------

Before import a database, make sure to change the timezone to `UTC` by running:

 ```
sudo mysql -e "SET GLOBAL time_zone = '+0:00';"

```

While this fixes it for your import, this value is being reset when you restart the MySQL instance. To make this change permanent, change it in `/etc/mysql/my.cnf`:

 ```
sudo nano /etc/mysql/my.cnf # linux
sudo nano /opt/homebrew/etc/my.cnf # macos

```

Add or change under the section `[mysqld]` the value for `default-time-zone`:

 ```
default-time-zone = "+0:00"

```

To make the change active, restart MySQL:

 ```
sudo service mysql restart # linux
brew services restart mysql # macos

```

### Subscribe to our newsletter

Do you want to receive regular updates with fresh and exclusive content to learn more about web development, hosting, security and performance? Subscribe now!

  Fill in your email address to receive updates  Subscribe 

#### Related articles

[Stream MySQL backup directly to S3 bucket](http://rocketee.rs/index.php/stream-mysql-backup-s3-bucket)
-------------------------------------------------------------------------------------------------------

When you import a MySQL database from a server to your local computer, you might notice that the dates and times are different.

[Read more →](http://rocketee.rs/index.php/stream-mysql-backup-s3-bucket)

[Export MySQL database using command line](http://rocketee.rs/index.php/export-database-mysql-command-line)
-----------------------------------------------------------------------------------------------------------

When you import a MySQL database from a server to your local computer, you might notice that the dates and times are different.

[Read more →](http://rocketee.rs/index.php/export-database-mysql-command-line)