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