6 ms·
Where I work we deal a lot with events (yoga gyms, community sports centers, etc.) and the old code was saving EVERYTHING in UTC. Every 6 months we'd have some
by Khao 11y ago
Where I work we deal a lot with events (yoga gyms, community sports centers, etc.) and the old code was saving EVERYTHING in UTC. Every 6 months we'd have some bug report coming in saying that some event was now one hour later or earlier on the website, or in some report, or in an admin edit screen.
I got fed up with this and migrated all the data so it's saved in local time with the timezone information in a separate column. If an admin creates an event for 4pm, we save 4pm with the event location's timezone. Since an event only takes place at one specific location, it makes no sense to have the data in UTC in the database. We can convert to UTC if we want but it's not really useful. The thinking is that if a client is shopping for a class in a different timezone, why would we convert the class's time to the client's timezone? It makes no sense. It's always in local time. This has helped so much.
- laut 11y agoYup. I wrote about this here: http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html http://www.creativedeletion.com/2015/03/19/persisting_future...
- Avernar 11y agoNice article. For your rule of thumb section there is a third case. For a future event that must happen x number of seconds/minutes/hours/days into the future you want to store it as UTC and not wall time. For example, the bypass valve on the boiler must be opened in 30 hours. You want that as UTC instead of wall time as you don't want a DST change to add an extra hour to that as you'd get a pile of scrap instead of an intact boiler.
- laut 11y agoGood point. For that that situation you would want to use something like UTC (or TAI). It is the duration from the starting point that we care about, not the future date.
- vemv 11y agoSeems a misguided practice to me. * Store datetime values in UTC. Else you get inconsistent values, which has unpredictable consequences. * Detect user's expected timezone using one of : a) user preference setting, b) the region the app is supposed to be running for, c) IP geolocation. Choice depends on the app requirements. Don't default to any timezone unless considered 100% safe. * Present DB values in the UI using said detected timezone. * UI-wise, slways display timezone information for datetime values. At the very least in a tooltip.
- Khao 11y agoIn our case, the admins who create/manage those events have to specify the event location, so we fetch the timezone for that location and if there's any mismatch, they can change the timezone manually. Since we migrated all the data, we have not received a single bug report for an issue that was caused by DST or from converting UTC times to local timezones.
- vemv 11y agoI retract my opinion - the approach is clearly correct for that business domain. The key difference is here: > The thinking is that if a client is shopping for a class in a different timezone, why would we convert the class's time to the client's timezone? It makes no sense. Roughly speaking, basically there are two kind of apps: - apps where datetime values need to be localized ("this message was sent at 18:00") - apps where datetime values need to reflect a fixed timezone ("we will meet in Madrid, at 18:00 local time")
- Khao 11y agoIn our app, everything that's a timestamp (invoices, payments, registering to an event, sending messages, create/update timestamps, etc..) is stored in UTC. Having both depending on the use case is really the best for us.
- laut 11y agoIt is not misguided, it is a good practice. Read more about why here: http://www.creativedeletion.com/2015/03/19/persisting_future_datetimes.html http://www.creativedeletion.com/2015/03/19/persisting_future...
- PaulWaldman 11y agoHow do you handle DST? Do any of these locations run 24/7? I've found DST to be a compelling case for storing everything in UTC. Times get converted to the correct local time when displayed in the UI.
- Khao 11y agoWe simply ignore DST. For example let's take a recurring event that happens every Wednesday at 6pm for 10 weeks. In the middle of those 10 weeks, this timezone enters DST. By saving the series' time as 6pm EST in the database, we can save all the individual occurences without having to do any check for DST changes in the middle of the series. The users are simply interested in knowing when their yoga class is, the class is at 6pm, that's it. Before or after DST change, it's still 6pm. Edit : Even if we had locations that were running 24/7, I don't think there would be any issue. An event that starts at 10pm and ends at 4am after a DST change? Any good datetime library will tell you that the event runs for either 7 or 5 hours, when calculating difference between those 2 datetimes when knowing the proper timezone. We're using C# and the standard .Net datetime library is one of the best I've ever worked with.
- icebraining 11y agoFunny; DST is exactly what made me doubt the rule of storing everything in UTC. We've had a lot of problems with a platform that stores recurring events as UTC date + time delta (eg. 1 week). What the users want is an event that happens every week on a certain weekday and time, in their timezone - they don't care if that falls on various times in UTC throughout the year. Of course, that's specific for recurring events/schedules.
- deleted 11y ago[deleted]