Recently I presented a talk about Timezones (which I hope to present a few more times). During the preparation I stumbled over a a function that eases timezone-based datetime-calculations in MySQL:
CONVERT_TZ. You can use it to convert from one timezone into a different one. So let’s say we have a database created like this:
CREATE TABLE `datetime` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `zeit` datetime DEFAULT NULL, `zone` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4; INSERT INTO `datetime` (`id`, `zeit`, `zone`) VALUES (1,'2014-03-04 12:23:34','Europe/Berlin'), (2,'2016-05-03 23:12:23','Europe/Busingen'), (3,'2016-05-03 23:12:23','America/Chicago');
So the dates in the table are in their respective timezones.
To check which Dates are before 14:00 UTC you can use a select-statement like this:
SELECT * FROM datetime WHERE TIME(CONVERT_TZ(zeit, zone, 'UTC')) < "14:00:00";
It will return something like this:
1 | 2014-03-04 12:23:34 | Europe/Berlin
3 | 2016-05-03 23:12:23 | America/Chicago
CONVERT_TZ you can convert a datetime (from a datetime-field) from a timezone into a different timezone. And the timezone can also be a field from the table.
The only prerequisit is that the timezone-informations have to be set up in the database. For that you’ll use the servers timezone-informations by calling
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql.
You can find more information on that at https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html and https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz
architectural daughtsman, brother, developer, father, husband, master of forestry sciences, scout