c# - Why is the date time offset the same for eastern and central time?

Question

I created a small app to test storing times with the UTC offset. I don't understand the results. If I run the program at 14:01 today, I get this:

This:

InsertPunch(new Employee { Id = 10, TimeZoneId = "Central Standard Time" });

Produces this in the DB:

2021-01-08 13:01:06.3594141 -05:00

This:

InsertPunch(new Employee { Id = 12, TimeZoneId = "Eastern Standard Time" });

Produces this in the DB:

2021-01-08 14:01:07.5587251 -05:00

The times are correct; the first is indeed CT and the second is indeed ET. But why is the offset -5 for both? Shouldn't the offset for CT be -6? I was assuming we could look at a time like this:

2021-01-08 14:01:07.5587251 -05:00

...and we know that UTC time is 19:01 (14:01 + 5:00). That's correct. But the results for CT are incorrect: 13:01 + 5:00 = 18:01, and the current UTC time is actually 19:01.

Am I understanding this incorrectly? Or perhaps I'm doing something wrong?

    static void Main(string[] args)
    {
        InsertPunch(new Employee { Id = 10, TimeZoneId = "Central Standard Time" });
        InsertPunch(new Employee { Id = 12, TimeZoneId = "Eastern Standard Time" });

        Console.WriteLine("Press any key to end.");
        Console.ReadKey();
    }

    private static void InsertPunch(Employee employee)
    {
        var punchTimeUtc = DateTime.UtcNow; // Need timestamp in UTC to properly convert to employee's time zone.

        var timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(employee.TimeZoneId); // Reference the employee's time zone.
        
        var punchTimeInUsersZone = TimeZoneInfo.ConvertTimeFromUtc(punchTimeUtc, timeZoneInfo);

        var punch = new Punch
        {
            EmployeeId = employee.Id,
            PunchTime = punchTimeInUsersZone
        };

        string sql = "INSERT INTO [time].[Punch] ([EmployeeId],[PunchTime]) VALUES (@EmployeeId, @PunchTime)";

        using (IDbConnection db = new SqlConnection(_connectionString))
        {
            db.Execute(sql, new { punch.EmployeeId, punch.PunchTime });
        }
    }

Answer 1

I believe what is occurring is that the field in your database is a datetimeoffset type (as evidenced by the offset appearing in the examples you pasted), but you are passing .NET DateTime values into the parameter as you insert data into that field.

In such a case, the SQL client will silently coerce those values using the same effects as described with .NET's DateTime to DateTimeOffset implicit conversion operator. From those docs:

... The offset of the resulting DateTimeOffset object depends on the value of the DateTime.Kind property of the dateTime parameter:

• If the value of the DateTime.Kind property is DateTimeKind.Utc, the date and time of the DateTimeOffset object is set equal to dateTime, and its Offset property is set equal to 0.

• If the value of the DateTime.Kind property is DateTimeKind.Local or DateTimeKind.Unspecified, the date and time of the DateTimeOffset object is set equal to dateTime, and its Offset property is set equal to the offset of the local system's current time zone.

In the code you gave, punchTimeInUsersZone.Kind will always be DateTimeKind.Unspecified and thus will use the system local time zone when deciding which offset to apply. Thus you store -5 because you are in the Eastern time zone and the date falls into the standard time period.

The resolution is to use .NET DateTimeOffset types in your code instead of DateTime.

• First, change the PunchTime property on your Punch class to DateTimeOffset.

• Then change the conversion logic in your InsertPunch method as follows:

  DateTimeOffset punchTimeUtc = DateTimeOffset.UtcNow;
  TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(employee.TimeZoneId);
  DateTimeOffset punchTimeInUsersZone = TimeZoneInfo.ConvertTime(punchTimeUtc, timeZoneInfo);
  

The rest is fine as is.