0

I have taken over a system that sends messages. The send date for the message is a TIMESTAMP(14) in MySQL. Messages can be scheduled to be sent in the future.

The system is based in the UK, where the timezones are either GMT or BST (GMT+1), depending on the time of the year.

In the existing code, the system sets the current timezone in MySQL using SET time_zone. Right now, the timezone is BST and as a result, when querying sent dates from, say January when the timezone was GMT, the results are shown as one hour off, however the dates for messages sent in April appear correctly.

If I remove the SET time_zone command, both dates appear correctly, however I believe that if (during BST) they schedule a message to be sent in November (which will be GMT again), then the messages will end up being sent one hour early, but I am not sure how to test and/or confirm this (other than by waiting until the timezones change).

How do I best cope with both situations, getting the (display) dates and setting the (send) dates correct for past, current and future messages?

Ben Holness
  • 2,457
  • 3
  • 28
  • 49
  • I don't have a solution to offer for your current situation, but this is why experienced database developers conclude that timestamp values should _always_ be stored in UTC (which is +0000 with no daylight savings time), and converted to the user's timezone only during display. – Bill Karwin Apr 08 '19 at 17:34
  • I thought that [MySQL Timestamps were implicitly stored as UTC](https://stackoverflow.com/questions/7029127/using-mysqls-timestamp-vs-storing-timestamps-directly) (see first answer)? Perhaps the issue is that the original developer explicitly set the MySQL timezone before saving the date? I will research further. – Ben Holness Apr 08 '19 at 17:49
  • Right, MySQL does convert TIMESTAMP to UTC based on the current MySQL server timezone setting. So if you go changing the timezone from time to time, or even per session, you get a collection of timestamp data in your table that is all over the map. One should set the MySQL server timezone to UTC and leave it that way, so no automatic conversion of timestamp values occurs. – Bill Karwin Apr 08 '19 at 17:55
  • Got it, so I will change it all to UTC going forward and have special code to handle older database entries I guess. Thanks. – Ben Holness Apr 08 '19 at 17:58
  • Yep, and then it's easier to code your app to support user preference for entering and displaying according to their own timezone. Even if they change their preference (like when they travel), it's pretty easy for the app to just show the timestamps in another timezone, since you'd convert the values during display regardless. – Bill Karwin Apr 08 '19 at 18:03
  • I mean it's easier that way than doing a lot of weird arithmetic. "Well, they entered the date when they were traveling in Europe, but they were scheduling a meeting with vendors from the US, and when the meeting actually happened the DST had changed, so... " – Bill Karwin Apr 08 '19 at 18:05
  • OK, so further research shows that the underlying MySQL timestamp is indeed UTC, but the issue is not entering and displaying according to the users' timezone, the issue is displaying the correct time when the date is in a different timezone from the user. Example: during GMT I send a message at 13:00 (GMT). If I look at the message it displays 13:00. Time passes, now it is BST and I look again at the message, but because I am now in BST it displays 14:00. I want it to show 13:00, as that is when it was sent. – Ben Holness Apr 10 '19 at 08:25

0 Answers0