CODEWITHSUNDEEP
mysqlphpmyadmin
sazr
sazr

Reputation: 25928

The dates changed after MySQL database get imported

I just exported a MySQL database from our old server using:

mysqldump -u [uname] -p[pass] db_name > db_backup.sql

I then imported the database file db_backup.sql into our new server using phpMyAdmin: 

PhpMyAdmin->Import->File to Import

The dates in the new database tables are different. Why have they changed and how can I stop this?

Here are the old database dates: enter image description here

And here are the new database dates, why are they changing?? enter image description here

Upvotes: 1

Views: 2041

Answers (2)

Dawid Karasiewicz
Dawid Karasiewicz

Reputation: 144

I'm not 100% sure, but maybe you have different time set on each of server. I see only days and hours have changed, so it's look like 18 hours. Check it out.

Change the global time_zone variable on the server: SET GLOBAL time_zone = 'America/Los_Angeles';

UPDATE the data after the fact, applying MySQL's CONVERT_TZ() function:

UPDATE my_table
SET    my_column = CONVERT_TZ(
                     my_column,
                     'America/Los_Angeles',
                     'Australia/Adelaide'
                   );

Upvotes: 0

Ave
Ave

Reputation: 4430

Solution 1:

Adding the --skip-tz-utc option to the export script.

Solution 2:

I think you should set timezone to UTC when importing data, not exporting.

From the documentation of --tz-utc option:

"mysqldump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file." So, the dump is in UTC. But since you use tab dumps, the "SET TIME_ZONE='+00:00" instruction is not executed, so you have to set the timezone on the connection manually.

Command: set time_zone=@old_time_zone

Upvotes: 2

Related Questions