George Bailey George Bailey -4 years ago 138
MySQL Question

MySQL: DATE_SUB/DATE_ADD that accounts for DST?

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 Source

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 ?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download