Always store dates/times in UTC (in the database)
Currently dates and times are stored in the database using the time zone specified by the "Server time zone" setting. This is dangerous and hugely problematic, especially for any site that wishes to use or display times in more than one time zone. And it makes determining durations between times practically impossible. This is because local times can have "gaps" because of daylight savings time.
The benefit to always using UTC times is HUGE:
1. There is absolutely no ambiguity (that, in itself, should be enough).
2. Calculating durations is extremely simple. The time span from 1:30AM UTC to 2:30AM UTC is always 1 hour. But if we happen to be "springing forward" or "falling back" and those same hours are stored in local time, that duration can be 0 or 2 hours!
3. No invalid dates. March 9th 2014, at 2:30AM Eastern Time does not exist. It is invalid -- it was skipped because of daylight savings time. But that is perfectly valid as far as the database is concerned.
4. No sorting/grouping problems. In US eastern time on November 2nd, 2014 1:59 AM can actually occur BEFORE 1:01 AM because of DST. This makes sorting basically impossible unless you first convert to UTC. But that's assuming you have some way to distinguish between 1:01 AM before DST started, and 1:01 AM after DST started. Which you don't. So this is effectively impossible.
Also, what happens if you need to move your server to a different time zone? You'll have to manually convert all of your dates to the new time zone. Or set your web server to the wrong time zone. The latter of which is the only option if you have a web farm with servers in different timezones.
This idea is not going to be covered in the upcoming version of Kentico. We are going to gather more information about it.
Platform Product Owner
Carl-Erik Kopseng commented
I find it quite amusing to see Dominik stating that he understands the advantages of UTC, then saying something that makes it quite obvious that he did not understand a quibble of the stuff Joe examplified.
All the examples Joe came up with are troubles that affect Kentico using local dates.
Sean Wright commented
I also agree this should be standard. The fact is, dates are horribly confusing and there is rarely consensus between development and business on how they should be handled.
The general user of a website doesn't understand dates/times/timezones let alone UTC but the nice thing it doesn't matter as long as the storage of the DateTime into the database is UTC. With that in place the application/frontend can display the date in whatever way is needed.
DateTime is considered to be a poor implementation for dates/time in the first place which is why there are libraries like NodaTime http://nodatime.org/.
At least standardizing Kentico to store in UTC would be a good start.
Actually, the only time I can see for storing times not in UTC is where special types of calendars are used. That's probably more experienced in the scientific community. When storing times in UTC in the database, yes, a little bit of extra logic and a tiny bit of care is needed in the code, but is nothing compared to the nightmare that can be faced when assuming you can store something in local times, then realising the application needs to be used internationally.
I have never, ever, seen a use case where times should not be stored as UTC. I always store times in UTC. To not do so just seems insane, especially for a multi timezone app (which is most apps I presume). But I assume there are reasons why people would prefer to store something as local time; if someone could enlighten me that would be great.
Joe Krill commented
Fortunately for me I've switched jobs and am no longer working with Kentico. But honestly, it's completely insane that we're on the verge of seeing version 9 of a product that is supposed to be an enterprise level solution, and this is *still* in the "Waiting for more supporters" state. This is really very basic stuff. And quite simply, it's a huge flaw in the system. This should be an absolute show-stopper for anyone considering this system. No sane developer would ever advocate for storing dates in local times except in very specific use cases (of which Kentico is not one).
A quick addendum: You can adjust your time zone for Azure Web Apps using the method described in this MSDN blog post: http://blogs.msdn.com/b/tomholl/archive/2015/04/07/changing-the-server-time-zone-on-azure-web-apps.aspx
Even then, this is still a hack/workaround, but thought I would post in case anyone needs it.
Is this in the pipeline at all? It is common practice to store all dates as UTC, and then use conversions to display dates where needed. Ever since we've moved to Azure Web Apps (which ONLY uses UTC servers), we have had to make these adjustments as well in other projects. Database fields should be (ideally) timestamps or datetimes with the assumption they are in UTC (not ideal, but much easier to convert to in a large project).
Because Azure Web App (formerly Web Site) servers are always in UTC, any built-in kentico functionality that relies on dates (e.g. the Publish From/To) will not function in the way that the dashboard administrator would expect.
another issue with not having things standardized to utc, is that when the server is in a different time zone than what the site is set to be at, you can't simply use a where clause as is within helpers such as TreeHelper.SelectNodes otherwise a query like "GETDATE() BETWEEN SomeStartDate AND SomeEndDate" fails, and cannot simply be fixed by using "GETUTCDATE() BETWEEN SomeStartDate AND SomeEndDate".
i understand that all kinds of technical implementation discussions would need to happen for this improvement to proceed however they're discussions that should be had sooner rather than later in moving forward - this should not be something that requires more supporters, it's something that is critical to enterprise systems.
Thank you Joe for additional input.
Joe Krill commented
Yes, it is very problematic! For the reasons I outlined above. Here's some real-world examples (using the fact that I'm in the US Eastern Time Zone):
- My database has the date March 9, 2014 02:30. What time is this in UTC (or any other time zone, for that matter)? (None. It doesn't exist. This date is invalid. But there's nothing preventing it from being in the database.).
- My database has the date November 2, 2014 01:30. What time is this in UTC? (It could be 05:30, or it could be 06:30, because this time occurs twice because of DST).
- Find all CMS.BookingEvent documents that are exactly 10 hours in duration. In can't be done reliably if I have an event that starts November 1, 2014 22:00 and ends November 2, 2014 08:00. On the surface that appears to be 10 hours. But it's actually 11, because the 01:00-02:00 hour occurs twice.
- Sort these dates: November 2, 2014 01:30 and November 2, 2014 01:31. Because both of these times occur twice, there's no way of knowing which time occurred before the DST change, and which after. 01:31 could actually come BEFORE 01:30.
- In 2007, our daylight savings time was extended 4 weeks. Kentico doesn't provide a way to have multiple rules for daylight savings times for different years -- there's just one single rule for each time zone. So now any dates before 2007 in my database may be calculated incorrectly.
- I now have another thing to manually track: Make sure my Kentico timezones are always up-to-date. It doesn't happen often, but time zone information DOES change (as it did in 2007 in the US). What happens when there's a slight discrepancy between my server time zone as it's defined by my computer, and the server time zone as it's defined by Kentico? I'm guessing Kentico will be storing the wrong dates at some point.
Sure, these are edge cases, but they are perfectly valid and certainly problematic.
I understand benefits of having time zone in UTC. But, Kentico stores datetime fields in database based on server environment. Do you see this problematic? Timezones influence primary output, not the input.