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.

This might helpful http://stackoverflow.com/questions/1646171/mysql-datetime-fields-and-daylight-savings-time-how-do-i-reference-the-extra (unlikely has a cure in mysql) — ajreal, Sep 07 '11 at 13:24
Boy! Do [I wish I could write a custom data type](http://dba.stackexchange.com/questions/5098/is-there-such-thing-as-custom-data-types) that works properly! — 700 Software, Sep 07 '11 at 14:19
You'll have to write you own DATE_SUB function that takes DST into account. BTW I would love to strangle whoever dreamed up that DST nightmare. — Johan, Sep 07 '11 at 15:12
Can't wait for 64bit unix timestamps to become the normal. y293billionK just won't be a problem... — Marc B, Sep 07 '11 at 15:24

score 4 · Answer 1 · answered Feb 27 '17 at 06:03

4

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'

answered Feb 27 '17 at 06:03

Garr Godfrey

8,257
2
25
23

Be careful with this answer, as convert_tz does not work with dates older than epoch (including the epoch) as well as beyond 2038: https://jira.mariadb.org/browse/MDEV-10135 – Buğra Gedik Mar 18 '19 at 19:49

score 4 · Answer 2 · answered Sep 07 '11 at 15:17

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 ;

score -2 · Accepted Answer · answered Sep 23 '11 at 11:30

-2

How would cutting off anything past 2038 be a real problem when you can be sure that 64bit integer timestamps will be immplemented everywhere 20 years before that at least ?

Seriously, there are so many issues with the datetime / timestamp types in MySQL that you should try and avoid them when possible.

Do you store many dates beyond 2038 ?

And, why not try using PostgreSQL which has much more advanced type support ?

answered Sep 23 '11 at 11:30

Morg.

697
5
7

Limiting to 2038 adds slight complexity. For example, repeating events on a calendar. Obviously 2038 is not a real problem, but I like to do things the right way, if there is a right way. Regarding the 4th paragraph, we are already on MySQL, I don't think that there is much reason to make such a major shift. – 700 Software Sep 23 '11 at 18:36

MySQL: DATE_SUB/DATE_ADD that accounts for DST?

3 Answers3