Correct way to handle date in querying db

Question

I want to know exactly what is the correct way to handle date as parameter in querying the db. My database is hosted in Windows Azure, and I have a table Job which as a field Modified with a DateTime data type. The DateTime is stored in the database as UTC.

I want to query the list of jobs based on the Modified date. The user will enter a start and end date, but they are based in different time zones. How do I handle the dates to match exactly the data when querying the database?

I'm using ASP.Net MVC. I also need to ensure that daylight saving time is considered.

I know I cannot just simply write a query like:

var data = _context.Jobs
                   .Where(c => c.Modified >= startDate && c.Modified <= endDate);

Answer 1

This is a hard problem since the user's browser might not report the correct time or timezone, but assuming you can trust that ...

... it's still a hard problem since you need to convert the time they entered in their local time zone to UTC and that involves knowing not just their timezone's offset (which is easy to get from the browser) but the actual timezone they are in so you can apply the appropriate offset for daylight savings or not for the datetime that they entered and not for 'now'.

This link might help you determine the browser's timezone and for actual calculations based on timezones you should look at Jon Skeet's Noda time library.

Most people punt on the daylight savings time issue and use the browser's timezone offset to adjust the entered datetime values and assume that when querying old data you don't care about a one hour error (or expand the query by one hour to include it regardless).

Answer 2

There are a few different ways to handle this. First of all, you are doing the right thing by storing the Job.Modified date as UTC. So don't change that. :)

Approach #1

• Ask the user for their time zone, usually in some kind of "preferences" or "settings" page in your application.
• If you want to stick with Windows time zones, you can produce a list from TimeZoneInfo.GetSystemTimeZones. But personally I don't like using Windows zones because they are a bit proprietary (see the time zone tag wiki). Instead, I prefer to let the user pick their IANA time zone using a map, with a JavaScript control such as this one.
• If you go with the IANA zones, you can use jsTimeZoneDetect to guess at the user's time zone. It is just a guess, so it can be the default selection. But don't lock the user into it. They should be able to change it.
• Since you know the time zone, you can use TimeZoneInfo or Noda Time to convert their input to UTC on the server, then query using UTC.
• Advantages: Can work with any time zone; Not dependent on the computer's time zone settings.
• Disadvantages: Lots of extra JavaScript. More user input required. More server-side code required. Possible dependency on third-party libraries.

Approach #2

• Use JavaScript to parse the text input into an actual JavaScript Date object.
• You may want to consider using moment.js for superior flexibility when parsing, but that is optional.
• Get the UTC time out of the Date or moment instance, and pass that to your server.
• Since the input time is now in UTC, you can use it directly in your query.
• Advantages: No extra user involvement for time zone selection. No additional libraries required (although some are optional).
• Disadvantages: Still requires some JavaScript. Can only work in the local time zone set on the user's computer. Values may be converted incorrectly for certain past dates near daylight saving transitions.