Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

SQL Dump Timestamp colums in UTC format

High-Voltage's profile image High-Voltage posted 4 months ago in General Permalink

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!

ansgar's profile image ansgar posted 4 months ago Permalink

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

Code modification/commit 2ea1939 from Ansgar Becker <anse@heidisql.com>, 4 months ago, revision 6327
Reset timezone in SQL exports to zero offset, to be independent from local date/times. See https://www.heidisql.com/forum.php?t=38078
High-Voltage's profile image High-Voltage posted 4 months ago Permalink

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).

ansgar's profile image ansgar posted 4 months ago Permalink

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.

High-Voltage's profile image High-Voltage posted 4 months ago Permalink

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?

High-Voltage's profile image High-Voltage posted 4 months ago Permalink

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.

Code modification/commit ee69a04 from Ansgar Becker <anse@heidisql.com>, 4 months ago, revision 6331
Set TIME_ZONE system variable for reading as well, not only for the export output. See https://www.heidisql.com/forum.php?t=38078
ansgar's profile image ansgar posted 4 months ago Permalink

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.

ansgar's profile image ansgar posted 4 months ago Permalink

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

High-Voltage's profile image High-Voltage posted 4 months ago Permalink

Can confirm it works! Thanks a lot :thumbsup:

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.