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
So with 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