Help answer threads with 0 replies.
Go Back > Forums > Linux Forums > Linux - Software
User Name
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.


  Search this Thread
Old 10-04-2009, 01:26 PM   #1
Senior Member
Registered: Dec 2004
Posts: 1,056

Rep: Reputation: 78
datetime values offset when restoring from db dump?

I have a pretty darn large database dump. I can see there are a few records in it with datetimes all around 15:00 in the afternoon:

(135312,302028,4,'2009-09-28 15:00:19','Jimmy'),
(135313,302029,4,'2009-09-28 15:08:57','formosa'),
(135314,302030,4,'2009-09-28 15:09:03','tammystrawn');
I have used the mysql command line to restore this dump into a clean database:
mysql -h -u user_name -p database_name < backup.sql
When I look in the database after restoring from the dump, i see those records in the correct table, but the time values are different. They are 08:00:19, 08:08:57, and 08:09:03, respectively.

Even more confusingly, I restored the same exact dump file on a different machine and got still different time values around 11:00.

WHAT THE HECK? What could possibly be causing these datetime values to be offset by several hours?
Old 10-04-2009, 06:57 PM   #2
LQ Guru
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195

Rep: Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043
Most likely the database from where the data was dumped used a different time zone setting than the one you are importing in.

Check both the setting of the time zone of the server, which is used by default by MySQL, or the setting of the global timezone in MySQL. In addition, each client can set a timezone for a session.

SELECT @@global.time_zone, @@session.time_zone;

Old 10-05-2009, 01:59 PM   #3
Senior Member
Registered: Dec 2004
Posts: 1,056

Original Poster
Rep: Reputation: 78
I have learned that this problem happens with timestamp fields because mysqldump will attempt to convert them to UTC by default. This seems like it would be a good way to ensure that the time is correct on any machine, but something was not working. I am now using mysqldump with the --skip-tz-utc option and this appears to result in correct time values.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql dump to convert values eodchop Linux - Software 5 05-29-2009 11:21 AM
Restoring individual database from a universal dump Roosta21 Linux - Server 2 11-10-2008 06:31 PM
MySQL :: Restoring Dump Swakoo Linux - General 6 09-09-2005 01:27 AM
Help restoring file permissions from a Dump bminish Linux - Newbie 3 12-12-2004 01:34 PM
How to forcely dump the history of user commands to the admin dump file. mcp_achindra Linux - Security 1 03-19-2004 12:04 PM > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 03:08 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration