When it comes to application development time, and by extension, dates are inevitable. We store them everywhere and for everything: user's date of birth, last updated date, product launch dates, reservation dates, order dates, and so much more. Furthermore, anyone that has developed an application knows that dealing dates and times can be frustrating, infuriating even. Just one user accessing your application from a different time zone can turn the functionality on its head and have you pulling out your hair.
The most common case is the most simple. A user creates an order and gives it a date which is stored in the database with the order. When another user pulls up the same order the date doesn't match. What happened, is there a user in a different timezone? Are you truncating for display and losing important information? Does your DatePicker control you use have a default time zone? All of these problems and many more can lead to hours of frustration.
So, how do we solve this problem? The biggest piece of the answer is planning and the rest are best practices. Starting with the idea of SQL Server DB, a .NET API, and a JS Front end, let's look at the different scenarios and what the best practices are for each and how we decide between them.
Question 1: Does the time matter?
Date of birth is perhaps the most obvious example of a clear yes for this question. Whether you are in LA, Chicago, or Perth, your date of birth is the same. Sure it may have been the next day in Melbourne, Australia when you made your glorious appearance but your birthday doesn't change when you visit. The trickier question might be order date. If I order a product that is going to be made in China does the order date change or do they just know that it represents that date of the location it was ordered in? That will be a question for your customer, but in our stack a 'Yes' leads to the following rules:
1. In SQL Server, use the date datatype. The temptation here is to just use a string, and if it is well formatted you can get a lot of similar functionality, but you are doing it the hard way, the slow way. Every sort and every date filtered search will take longer and in the end to use it you will still have to convert it back to a date object to use it.
2. Anywhere you need to put it into a date time object, put it in UTC and keep it that way. Pickers, fields, js packages: you can send the timezone on all of them and they should all be set to UTC. If you don't you'll suddenly have your date picker converting your date to local time and your day will jump, so be careful.
Question 2: Time matters, but is it relative?
At its heart, this is a display question, once we have decided that time is important and needs to be stored it's all about user experience. Does the display change to update with the user's time zone or do they just need to see it as it was created. Either way the first four rules are the same:
1. Use datetimeoffset - This one is pretty simple. This gives your storage for the date, the time, and the timezone offset. Everything you need.
2. Always store the timezone at creation time of the created location. Why not just in UTC, you ask ? - Data loss. If you are always displaying the date/time in the user's locale UTC is fine, until someone changes their mind. With careful programing can support both display options and preserve the location data, just in case. If you are displaying to users the date as it was created then you will need that data to meet the requirements.
3. Always pass through date time objects with max specificity. This is where things most often go wrong in the development side. We often think of the app and server and db as in the same time zone but that is often not the case. If we pass through objects without the timezone, 5:00PM CST can quickly become 5:00PM UTC. And if you insert into the DB without specifying a timezone it will pick UTC for you and the locale data is lost. On the front end side you will need a library like day.js or moment-timezone.js to make sure you are keeping all that information.
4. Always display with a timezone. Users need information about what they are seeing and if you are displaying a date and time they shouldn't have to guess if it's local to them or the initial entry. The space it takes up is minimal, so there is no excuse not to show it.
//Bad - What date timezone are you selecting in, what date and timezone are you saving in? You may never know This library will use the data of the defaultValue.
<DateTimePicker
value={value}
onChange={setValue}
/>
//Better - Clear to the user and data value is well known.
<DateTimePicker
timezone={currentTimezone}
value={localizedValue}
onChange={setValue}, label={timezoneLabel}
/>
If you display the date to users in the timezone they are in, then you are done with the rules. All that is left is to make sure you are using a library like moment-timezone.js to convert and format the data properly.
If you are displaying the date to users in the original timezone then you may have one additional step. When it comes to timezones there is more than one location that uses -6:00, but displaying the date with the offset and not the abbreviation is nearly always abrasive to users. So, if you are a global organization and you want to know, not guess, the correct timezone, then you will need to store the locale as well. Libraries like moment-timezone can use this additional data to allow you to format the date in the way that is most pleasing to the eye.
Conclusion
Using dates and times can go wrong in the planning or the execution stage and the developers, consultants and clients must all be on the same page to create a solution that works for everyone. But answering a few quick questions and following a few simple rules will set you and your customers up for success.
