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

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 ?