Asp.Net, SQL and TimeZones

Question

It's been asked, but I am battling to grasp the concept of how to handle timezones in a web app. I have a system that tracks the progress of projects. I have a ProjectStartDate DATE in my SQL Server database. (There's a few more fields and tables, but lets focus on one).

The server is located somewhere in the United States. I live in Australia.

Calling SELECT GETDATE() returns "2013-08-11 14:40:50.630" My system clock shows "2013-08-12 07:40"

In my database, I have 'CreateDateTime' columns on all tables. When I store that, within my c# code, I use CreateDate = DateTime.UtcNow

I use that, as I heard it was better to use UTC.

But, when a user is presented with a calendar control, and they select a Start Date for a project, I store what ever the user selected. No conversion... And as I said, the StartDate is a DATE type in the database.

The problem is, if a project started today - my front end says that the current project is No Started, because the server is still in Yesterday.

I think the dates should be stored as I am storing them. But maybe I need to somehow get the users timezone, and apply that on the UI level?

Problems I see are:

• I don't know the users timezone. Add something to allow them to select it?
• The status of the project maybe determined in a stored procedure, so when can I apply the conversion? In the proc, it might do a check, and return a VARCHAR stating "Not Started" if the StartDate <= DateTime.Now?

I use EntityFramework and Linq to get data most of the time. I need a strategy for inserting and retrieving data, both from a SQL sense, and a .Net sense.

I have added code to get the user to select their timezone based on this:

public List<TimeZoneDto> GetTimeZones()
{
    var zones = TimeZoneInfo.GetSystemTimeZones();
    var result = zones.Select(tz => new TimeZoneDto
        {
            Id = tz.Id, 
            Description = tz.DisplayName
        }).ToList();

    return result;
}

That is then persisted in their profile.

All dates are being stored as UTC, as advised in the answers below.

I'm still confused how to handle the dates when they go to and from the database, to the client. Here is an example of how I am storing a record:

public int SaveNonAvailibility(PersonNonAvailibilityDto n)
{
    person_non_availibility o;

    if (n.Id == 0)
    {
        o = new person_non_availibility
            {
                PersonId = n.PersonId,
                Reason = n.Reason,
                StartDate = n.StartDate,
                EndDate = n.EndDate,
                NonAvailibilityTypeId = n.NonAvailibilityTypeId,
                CreateUser = _userId,
                CreateDate = DateTime.UtcNow

            };
        _context.person_non_availibility.Add(o);
    }
    else
    {
        o = (from c in _context.person_non_availibility where c.Id == n.Id select c).FirstOrDefault();
        o.StartDate = n.StartDate;
        o.EndDate = n.EndDate;
        o.Reason = n.Reason;
        o.NonAvailibilityTypeId = n.NonAvailibilityTypeId;
        o.LastUpdateDate = DateTime.UtcNow;
        o.LastUpdateUser = _userId;
        o.Deleted = n.Deleted ? DateTime.UtcNow : (DateTime?)null;
    }
    _context.SaveChanges();
    return o.Id;
}

This method basically saves when a person is not available for work. Note the way I store the LastUpdateDate. Also, the 'Start' and 'End' dates. Those dates are more 'Business' dates.

On selection, and then date checking, is where I have issues. In this example, I am getting a persons rate of charge, based on NOW.

public decimal CurrentRate
{
    get
    {
        if (ResourceCosts != null)
        {
            var i = ResourceCosts.FirstOrDefault(t => DateTime.UtcNow <= (t.EndDate.HasValue ? t.EndDate.Value : DateTime.UtcNow) && DateTime.UtcNow >= t.StartDate);
            if (i != null) return i.Cost;
            return 0;
        }
        return 0;
    }
}

So, what I am wanting to do here, is, based on the current date, I want to see his rate (As his charge our rate maybe be $100 from the 1st Jan, to the 15th Jan, and then $110 from the 16th until the 31st of Jan. So, I look for the rate applicable today (if any). This doesn't work across time zones, and it's maybe here where I need to do some date manipulation based on the 'DateTime.UTCNow'?

Note, I now know the users timezone based on the code above where I am storing his timezone. I can use that somehow here? Maybe, when the user logs in, grab the date info from his profile (Zimezone info), and then have a global shared function, that returns the users datetime, based on adding or removing hours from the UTC Date... and using that where ever I am doing DateTime.UTCNow?

Hope someone can guide me.

Answer 1

You may find that there is not one single "right" way to handle all of this. There are multiple approaches to the several different problems you describe in your question. I will attempt to clarify a few points.

• First, don't ever attempt to think about local time on a server. Your code and data should not have to change based on where you deploy it. You said your server was in the USA, but there are multiple time zones to consider, and many servers will have their time zone set to UTC regardless of their physical location.

  You should avoid GETDATE() or SYSDATETIME() in SQL Server. If you need a current timestamp in SQL, use GETUTCDATE() or SYSUTCDATETIME(). If for some reason the server's time zone is important to you, use SYSDATETIMEOFFSET() instead.

  Likewise, avoid using DateTime.Now in .Net from any server-side code. Use DateTime.UtcNow or DateTimeOffset.UtcNow for a UTC timestamp, or use DateTimeOffset.Now if for some reason the server's time zone is important to you.

  You can read more about this in my blog post: The Case Against DateTime.Now

• Next, let's talk about the data type you're using. The date type in SQL Server stores just a date. That's it. No time, no offset, and no time zone. An example would be 2013-08-11. You should use it when you really mean a whole calendar date. There is no worldwide uniform context of "today". Instead, everyone has their own meaning based on their time zone. Also, not every calendar day is 24 hours in length. A day could be 23, 23.5, 24, 24.5 or 25 hours long, depending on how daylight saving time is applied in the particular time zone, and if you are evaluating the day of a DST transition.

  In .Net - there is no Date type, so a SQL date is converted to a DateTime with the time set to midnight (00:00:00), and the kind set to Unspecified. But don't fool yourself - the time isn't suddenly midnight, we are just filling in zeros for the time. This can lead to a lot of error and confusion. (If you want to avoid that, you can try Noda Time, which has a LocalDate type for this purpose.)

• What you really need to be thinking about, and haven't defined in your question, is this:

  What exact moment does a project start?

  Right now you are just saying 2013-08-11, which doesn't refer to a specific moment in time. Do you mean the beginning of that day in a particular time zone? Or do you mean the beginning of that day according to the user's time zone? Those might not be the same thing. You can't compare to anyone's "now" (utc, local, or otherwise) unless you know what moment in time you are talking about.

  If the project starts at an exact moment in time worldwide, then the easiest thing would be to store a datetime (or datetime2) type that contains that precise time in UTC. So you might say that a project starts at 2013-08-10T14:00:00Z - which would be exactly midnight on August 11th in Sydney, Australia. In .Net, you would use a DateTime type with the .Kind set to Utc.

  Another way you could represent this is by storing a datetimeoffset type that has a value of 2013-08-11T00:00:00+10:00 - which is the same moment in time, but uses the offset to give you a value that is pre-converted. (Sydney is at UTC+10 on that date). You would use the DateTimeOffset type to work with this in .Net.

  But if the project starts at different times depending on the user, then it's not really an exact moment in time. It's more of a "floating" start. If users from different places around the world are assigned to the same project, then some users could be starting before others. If that's your intention, then you can just use the date type if all projects start at midnight, or you can use a datetime or (datetime2) type if projects might start at different times. In your .Net code, you would use a DateTime type with the .Kind set to Unspecified.

• With regard to getting the user's time zone, the best thing you could do would be to ask them. Despite the common misconception - you can't just get it from the browser. All you could tell from the browser is what their current offset is. (Read the "TimeZone != Offset" section of the timezone tag wiki).

  When asking the user for their time zone, if you decide to use Windows time zones you can produce a dropdown list from the TimeZoneInfo.GetSystemTimeZones method. The .Id is the key you store in your database, and you show the .DisplayName to the user. Later you can use the TimeZoneInfo.FindSystemTimeZoneById method to get a TimeZoneInfo object that you can use for conversions.

  If you wanted to be more precise, you could use IANA time zones instead of the Windows time zones. For that, I recommend using a map-based timezone picker control, such as this one. You might also use jsTimeZoneDetect to guess a default value for your control. On the server you would use Noda Time to perform time zone conversions.

• There is an approach that doesn't require time zone conversions. Basically, you do everything in UTC. That includes transmitting the time to the browser in UTC. You can then use JavaScript to get the user's current time in UTC and compare against that.

  You can use various functions of the JavaScript Date class to do this if you wish. But you may find it easier to work with a library such as moment.js.

  While this approach is viable for many things, security is not one of them. Your user can easily change the clock of their computer to work around this.

• Another approach would be to compare server-side against UTC. If you have the exact UTC starting time in your database, then you can just check DateTime.UtcNow in your .Net code and use that to decide what to do. You won't need the user's time zone to make this comparison, but you will need it if you want to show them what that means in their local time.

I hope this clears up the confusion and didn't make it worse! :) If you have additional concerns, please edit your question or ask in comments.

UPDATE

In response to your updated question, I suggest you try the following:

var timeZoneId = "Eastern Standard Time"; // from your user's selection
var timeZone = TimeZoneInfo.FindSystemTimeZoneById(timeZoneId);
var nowInTimeZone = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, timeZone);
var todayInTimeZone = nowInTimeZone.Date;

var i = ResourceCosts.FirstOrDefault(t => t.StartDate <= todayInTimeZone &&
            (!t.EndDate.HasValue || t.EndDate >= todayInTimeZone));

Of course this means that in your StartDate and EndDate fields, you are not storing these as UTC - but rather as the "business dates" that are relevant to the user. These only line up to a specific moment in time when you apply a time zone, so the same UTC timestamp could fall on different dates depending on what the user's time zone is.

Also, you are using fully inclusive ranges, which is usually OK for these kind calendar date ranges. But make sure you realize that there could be overlap. So if you have 2013-01-01 - 2013-02-01 and 2013-02-01 - 2013-03-01, then there is that one day 2013-02-01 that is in both ranges.

A common way around this problem is to use half-open intervals, [start,end). In other words, start <= now && end > now. But this is more common when using a full date and time instead of just a date. You might not need to do this, but you should at least think about it for your particular scenario.