MySQL: DATE_SUB/DATE_ADD that accounts for DST?

Question

This returns 1 (aka TRUE)

SELECT DATE_SUB(NOW(), INTERVAL 24*100 HOUR) = DATE_SUB(NOW(), INTERVAL 100 DAY);

100 days ago, the hour of day does not change. But due to Daylight Savings Time (US), 100 twenty-four hour periods ago is actually one hour earlier than if you counted by days. If the above statement accounted for DST, it would return 0 or FALSE.

Is there a way I can say to account for DST for a given statement or session? I would prefer not to use UNIX_TIMESTAMP since it cuts off anything past 2038.

Answer 1

You'll need to create a custom function, something like this.

DELIMITER $$

CREATE FUNCTION DST(ADatetime DATETIME) RETURNS DATETIME
BEGIN
  DECLARE result DATETIME;
  SET Result = ADatetime;
  IF ADatetime >= startDST AND ADateTime <= endDST THEN 
    result = DATE_SUB(ADatetime, INTERVAL 1 HOUR);
  END IF;
  RETURN result;
END $$

DELIMITER ;

Answer 2

This is really just a matter of converting to UTC and back:

 CONVERT_TZ(DATE_SUB(CONVERT_TZ(NOW(),@@session.time_zone,'UTC'), INTERVAL 24*100 HOUR),'UTC',@@session.time_zone);

This assumes you have the timezone tables set up to use named time zones. If not, you can use '+0:00' instead of 'UTC'