SQL Dump Timestamp colums in UTC format

Hi all!

I couldn't find the answer through the search, although it seems such a basic question. So hopefully, it's not a double one.

Here we go: When dumping a MySQL table to a .sql file using HeidiSQL, the timestamp columns are converted back to local time. This makes the export impossible to import again due the risk of time shifts because of daylight saving time etc... The default action for a mysqldump is to export all timestamps in UTC format and add following controls to the dump:

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
--> export data here <-- 
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

This way the export/import is consistent and works just fine.

Unfortunately, I can't find this option in HeidiSQL. Where can I find it? If it's not in there, please let this be a feature request ;-)

Thanks!

There is no such option yet. I can add that to the export generation.

Thanks for the prompt reply. Any manual workaround for the moment? I read something about setting the session time-zone. But I'm not sure if a) I don't mess up the MySQL server when fiddling around with this and b) does this really do the trick (I can test when a is confirmed not to be harmful).

No need for a workaround. My modification is already compiled in the latest build 11.3.0.6327. Just update through Help > Check for updates.

Awesome! Thanks for the quick action. Unfortunately, the values didn't change.

Original export using mysqldump:

/*!40000 ALTER TABLE `Measurements` DISABLE KEYS */;
INSERT INTO `Measurements` VALUES (1,'2015-08-30 16:18:18',1,258),(2,'2015-08-30 16:18:18',2,1),(3,'2015-08-30 16:20:02',1,25),(4,'2015-08-30 16:20:02',2,0)

Export using HeidiSQL:

/*!40000 ALTER TABLE `Measurements_tmp` DISABLE KEYS */;
INSERT IGNORE INTO `Measurements_tmp` (`Date`, `Sensor`, `PulseCount`) VALUES
    ('2015-08-30 18:18:18', 1, 258),
    ('2015-08-30 18:18:18', 2, 1),
    ('2015-08-30 18:20:02', 1, 25),
    ('2015-08-30 18:20:02', 2, 0),

Note the two hours difference (currently GMT+2). So still exporting in local time zone.

I see indeed the addition to the export which is required to do the import.

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;

Could it be related to my MariaDB Server?

Seems like I cannot change my previous post.

When I first do a query like: SET TIME_ZONE='+00:00';

The export is correct.

Afterwards I can set the Time zone back with: SET TIME_ZONE='system';

However, I'm not sure if this change is for my session only, or if I change everything that is communicating with the server. Currently just deving on a test machine.

My previous modification only added the SET TIME_ZONE.. to the output. I think I need to set the time zone for reading values as well. Will do that.

And yes, HeidiSQL always uses a single connection, so any variable setting will affect the whole communication, until you disconnect or probably the connection breaks due to network issues.

Should now work, with the latest build. Please update.

Can confirm it works! Thanks a lot :thumbsup: