DateTimeOffset vs DateTime
All of the dates in our tables are DateTime (sql version mainly) and all the dates are stored as local time (dumb design mainly). I am slowly modernizing the backend and slowly copying all the domain logic into .NET Standard libs so we can transition the app from .NET Framework to .NET 8+. Since none of the new code is being used by the app yet, I figured now might be a good time to fix our local DateTime dumb design.
1) keep new entities using DateTime properties and table stays u changed
2) new domain models uses DateTimeOffset for those properties
3) mapping layer handles converting DateTime to DateTimeOffset and vice versa when moving data in/out of persistence.
That way when we can transition the tables to be DateTimeOffset we can support that by just updating the entities and patching the mapping layer.
How does this sound?
14 Replies
The approach that I've used is to only use DateTime in UTC on the server. The database only stores UTC values, and the server side doesn't need to know anything about a TZ. When communicating with a client, I do the TZ conversion on the client side. This allows the server to remain TZ ignorant, and the client to be able to display values in the local time. I've never really understood why DateTimeOffset needs to exist. Ultimately, it represents the same information as a UTC DateTime: and instant in time. For future scheduling you'd still need to store a "local" time and a full TimeZone name, so DTO doesn't really solve any problem that I'm aware of.
That used to be my preference as well until I came across
DateTimeOffset. Seems like it offers a lot of benefits when I do have to track timezone server side.
We don’t have a system where the user selects their preferred timezone
Everything is saved under one time zone
It’s dumb I know, but this is a legacy app
If I worked with UTC I would take the same approach except convert local to UTC and keep DateTime.
All the legacy with none of the modern features
Technical or otherwisewhen I do have to track timezone server side.The problem is that DateTimeOffset doesn't track the timezone. It tracks the offset only, which isn't enough to know the TimeZone.
If it's helpful, here is some code that I wrote a while back to convert date time values in a SqlServer database between different timezones. I needed it to convert from UTC to "local", to support a reporting tool that was TZ ignorant. You could use it to convert from "local" to UTC.
https://gist.github.com/MarkPflug/af84393127141e04ef0aa22ff156cda8
It will update all datetime/datetime2 columns in the target database.
we usually store DateTimeOffset + TImeZone
doesnt datetimeoffset already have timezone in it?
yes, i mean i'm not storing just the utc value, but utc + original timezoneinfo
No. DateTimeOffset doesn't store a timezone. It stores an offset. There isn't a 1 to 1 correlation between timezone and offset, so they aren't the same thing.
For the most part, the only time you need TimeZone, is when scheduling things in the future. The reason offset isn't enough in this case, is that TimeZones occasionally change: adopting or abandoning daylight savings is probably the most common example.
This talk by Jon Skeet does a good job of explaining things: https://www.youtube.com/watch?v=64X8rCy1jSA
I thought he had a blog post too, but I can't seem to find it now.
NDC Conferences
YouTube
Dates and times aren't that hard - honestly! - Jon Skeet
Like floating point and Unicode handling, date and time is one of those areas many developers are afraid of. That's understandable, but is curable.
The date/time handling in .NET feels okay, until suddenly it's not - we'll look at the root causes, and how you can think about date/time information (even time zones) without fear. It's just possibl...
But, for the sake of recording when something has already happened, UTC is really all you need.
Trying to decide if we should go DateTimeOffset or DateTime and store UTC
What are you planning on doing with the Offset? Essentially, DTO is just an inefficient means of storing UTC.
I’m not sure. I don’t think we need it
I’m going to add a tech debt story to convert all the dates in the db to UTC time and do a code change at the same time