Remove Time Zone Offset from DateTimeOffset?

Question

This code:

DateTimeOffset testDateAndTime =
    new DateTimeOffset(2008, 5, 1, 8, 6, 32, new TimeSpan(1, 0, 0));

//CLEAN TIME AND DATE 
testDateAndTime = testDateAndTime.DateTime.Date; 

var datesTableEntry = db.DatesTable.First(dt => dt.Id == someTestId);
datesTableEntry.test= testDateAndTime;

db.SaveChangesAsync(); 

...produces this result in my database: 2008-05-01 00:00:00.0000000 -04:00

How should I revise my code so that it changes the time zone offset from -4:00 to +00:00 in testDateAndTime?

I have also tried:

public Task<DateTimeOffset> SetTimeZoneOffsetToZero(DateTimeOffset dateTimeOffSetObj)
{
    TimeSpan zeroOffsetTimeSpan = new TimeSpan(0, 0, 0, 0, 0);
    return dateTimeOffSetObj.ToOffset(zeroOffsetTimeSpan);
}

...but that code doesn't do anything.

My end goal is just to have a date without a time or a time zone offset. I do not want to convert the time to another time zone. (That is, I don't want to subtract 4 hours from the 00:00:00.0000000 time and remove set time offset to +00:00. I just want to set the offset to +00:00.)

Here is another approach that I came across elsewhere:

DateTimeOffset testDateAndTime =
    new DateTimeOffset(2008, 5, 1, 8, 6, 32, new TimeSpan(1, 0, 0));

testDateAndTime = testDateAndTime.DateTime.Date; //Zero out time portion

testDateAndTime = DateTime.SpecifyKind(
    testDateAndTime.Date, DateTimeKind.Utc); //"Zero out" offset portion

I was sure that SpecifyKind would convert my DateTimeOffset. That is, change both the time and the time zone offset. But, my test indicates that this code just changes the time zone offset, which is what I want. Is there a problem with doing it this way?

Stgermain

You need to think of this a little different. You always have a timezone and a time component. You can set the time to 0 (midnight). But you need to do so for a specified timezone. What you probably want is date.Utc.Date to get the UTC timezone and then the Date zeros out the time part. – Sympathetic

@DavidThielen If I do date.Utc.Date, will it return a DIFFERENT date then the one I have if it's on the edge? – Stgermain

Good point. Maybe Date.Utc.Date? – Sympathetic

There's a lot of information missing here. What database platform are you using? What data access technology is this? (EF maybe?) What is the field's data type in the database? What data type is the test property? What code are you using to retrieve the data where you see the -04:00 offset? Likely the offset is being shown because there's a conversion to the local time zone during retrieval, but you didn't show that part. An MCVE.would be helpful. – Minta

db: SQL, field date type: datetimeoffset(7), test prop: DateTimeOffset, "retrieving data": I am looking at the data in SQL Server manually @DavidThielen Trying that now. – Stgermain

Actually, after a second look, I see the problem. Answer forthcoming shortly... – Minta

I appreciate any help. I am guessing I am missing something obvious, but I have been at this for a while now. – Stgermain

Answer 1

The issue doesn't have anything to do with the database actually. If you set a breakpoint or log the output somewhere, you should be able to see the offset being tacked on shortly after this code:

testDateAndTime = testDateAndTime.DateTime.Date;

Let's break this down:

• You started with a DateTimeOffset value of 2008-05-01T08:06:32+01:00
• You then called .DateTime, which resulted in a DateTime value of 2008-05-01T08:06:32 with DateTimeKind.Unspecified.
• You then called .Date, which resulted in a DateTime value of 2008-05-01T00:00:00 with DateTimeKind.Unspecified.
• You assign the result back to testDateAndTime, which is of type DateTimeOffset. This invokes an implicit cast from DateTime to DateTimeOffset - which applies the local time zone. In your case, it would appear the offset for this value in your local time zone is -04:00, so the resulting value is a DateTimeOffset of 2008-05-01T00:00:00-04:00, as you described.

You said:

End goal is just to have a date without time or time zone offset.

Well, there is currently no native C# data type that is just a date without a time. There is a pure Date type in the System.Time package in corefxlab, but that's not quite ready for the typical production application. There's LocalDate in the Noda Time library that you can use today, but you'd still have to convert back to a native type before saving to the database. So in the meantime, the best you can do is:

• Change your SQL Server to use a date type in the field.
• In your .NET code, use a DateTime with a time of 00:00:00 and DateTimeKind.Unspecified. You'll have to remember to ignore the time portion (as there are indeed dates without a local midnight in certain time zones).
• Change your test prop to be a DateTime, not a DateTimeOffset.

In general, while DateTimeOffset fits a large number of scenarios (such as timestamping events), it doesn't fit well for date-only values.

I want the current date, with zero offset.

If you really want this as a DateTimeOffset, you'd do:

testDateAndTime = new DateTimeOffset(testDateAndTime.Date, TimeSpan.Zero);

However, I advise against this. By doing so, you're taking the local date of the original value and asserting that it is in UTC. If the original offset is anything other than zero, that would be a false assertion. It is bound to lead to other errors later, as you're actually talking about a different point in time (with potentially a different date) than the one you created.

Regarding the additional question asked in your edit - Specifying DateTimeKind.Utc changes the behavior of the implicit cast. Instead of using the local time zone, it uses UTC time, which always has an offset of zero. The result is the same as the more explicit form I gave above. I still recommend against this, for the same reasons.

Consider an example of starting with 2016-12-31T22:00:00-04:00. By your approach, you'd save into the database 2016-12-31T00:00:00+00:00. However these are two very different points in time. The first one normalized to UTC would be 2017-01-01T02:00:00+00:00, and the second one converted to the other time zone would be 2016-12-30T20:00:00-04:00. Notice the change of dates in the conversion. This is probably not the behavior you'd want creeping into your application.

Minta

All of these concerns are valid, however sometimes the input should be treated in timezone-agnostic manner, such as in the case of date of birth. And it's unwise to leave this concern for the interacting representation layer, as it might be poorly supported looking at you JavaScript. – Submission