0

I've got a question about proper date computation that leverages MySQL's ability to work with local timezones.

Consider this case:

I reside on East Coast of United States, in a state that observes daylight savings time (DST). In fall, one day gets an extra hour (clock turned back), meaning 25 hours in the day, while in spring, one day gets one hour less (clock turned forward), meaning 23 hours in the day. Therefore time computations based on timestamps in local timezone should account for the change.

So, here's an SQL example:

SELECT TIMEDIFF('2020-03-08 06:00:00','2020-03-07 06:00:00');

The expected returned value is:

23:00:00

Actual returned value:

24:00:00

Which is not correct.

FACT:

Today is 5/28/2020 and my current timezone is EDT.

FACT:

2020-03-07 DOES NOT EXIST in EDT, it is IMPOSSIBLE. EDT does not exist during that part of year.

FACT:

2020-03-08 06:00:00 shouldn't be EST during that part of the year for locations observing DST, that timestamp should be treated as EDT.

THEREFORE:

MySQL cannot (for reasons of obvious time logic) treat both of those timestamp parameters as the same timezone.

In this case the behavior should be such:

Current timezone for session is EDT

function(detected EST, detected EDT)

So, what MySQL query will return the time difference between 2020-03-08 06:00:00 and 2020-03-07 06:00:00 as 23 hours for the above scenario?

The query should be generic and should not make assumptions about the current timezone of the parameter.

Help is appreciated. Thank you!

r00tb33r
  • 1
  • 3
  • I've seen that thread. There are a couple of reasons why that thread doesn't address my concern. #1 is that I am concerned with time computation, not storing values, as from what I've read MySQL stores values in UTC as-is, and converts to session timezone on retrieval. #2 It is an 11 year old post, and the DST problems were certainly present 11 years ago, however I (and many other users) are hopeful that in the past 11 years improvements have been made or someone has come up with an elegant pure-SQL solution to time computation with DST. – r00tb33r May 28 '20 at 23:39
  • the only chance i see that you check if the dates correspond to your dst and add or subtract your data.on computers for 24h monitoringm, the dst is disabled, so that the monitored time is without breaks. – nbk May 28 '20 at 23:57
  • Is your mysql.time_zone_transition table populated? – Shadow May 29 '20 at 02:21
  • I would be tempted to edit the question at the very outset, to make it abundantly clear that you're aware of the duplicate, and why you think it fails to address your issue. – Strawberry May 29 '20 at 06:43

0 Answers0