We had some issues with DST changes last weekend. We have an application that uses local time for data entry and performing lookups (search ranges for SQL) in the database. Due to the fact that we where at UTC+2 last week and UTC+1 this week (absolutely spoken atleast), requesting data today concerning last week becomes an issue. Therefor we need to check our input dates to see if they fall in the ‘wintertime’ or ‘summertime’ part. We came up with an interesting solution. The following is a mysql function that will take in a UTC/GMT datetime and it will tell you what the time was (back then, taken into account DST). This is hardcoded for our timezone here. We needed a function because we had to use this in a trigger. This works like a charm for us, maybe we reinvented the wheel here but it sure works.
DROP FUNCTION IF EXISTS `test`.`ConvertUTCToCurrentTZ` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `ConvertUTCToCurrentTZ`(ts TIMESTAMP) RETURNS TIMESTAMP
DETERMINISTIC
BEGIN
DECLARE swS TIMESTAMP; -- Switch summer time
DECLARE swW TIMESTAMP; -- Switch winter time
-- Time switches: 0200 -> 0300 last sunday of march
-- Time switches: 0300 -> 0200 last sunday of october
IF(WEEKDAY(CONCAT(EXTRACT(YEAR FROM ts), '-03-31')) = 6) THEN
SET swS = CONCAT(EXTRACT(YEAR FROM ts), '-03-31 01:00:00'); -- UTC Time of the switch moment is 01
ELSE
SET swS = CONCAT(EXTRACT(YEAR FROM ts), '-03-31 01:00:00') - INTERVAL (WEEKDAY(CONCAT(EXTRACT(YEAR FROM ts), '-03-31')) +1
) DAY;
END IF;
IF(WEEKDAY(CONCAT(EXTRACT(YEAR FROM ts), '-10-31')) = 6) THEN
SET swW = CONCAT(EXTRACT(YEAR FROM ts), '-10-31 01:00:00'); -- UTC Time of the switch moment is 01
ELSE
SET swW = CONCAT(EXTRACT(YEAR FROM ts), '-10-31 01:00:00') - INTERVAL (WEEKDAY(CONCAT(EXTRACT(YEAR FROM ts), '-10-31')) +1
) DAY;
END IF;
IF(ts >= swS AND ts < swW) THEN -- Summertime
RETURN CONVERT_TZ(ts,'+00:00','+02:00');
ELSE
RETURN CONVERT_TZ(ts,'+00:00','+01:00');
END IF;
END $$
DELIMITER ;
It’s a kinda lazy way of doing so, in fact one should design applications to just use GMT/UTC everywhere and then program accordingly. Then you just need to know in what timezone your (web)user resides to know the offset of the data. This is a fix to transparently modify dates to/from GMT thrown to the database by unaware php code. It just beats fixing the code everywhere. I’m fully aware this isn’t the greatest solution.