Storing Datetime (Utc) VS. Storing Datetimeoffset

Is there any sense to store a UTC date in DateTimeOffset Column instead of DateTime(2)?

UTC being stored in a datetimeoffset makes sense, provided you're going to be storing other timezones too. If every time you're going to store is UTC, then you might as well use datetime2 (or possibly smalldatetime depending on your accuracy requirements).

Even if all your data is for one country, but that country observes DST, then it might be worth using datetimeoffset. then times in the summer might be UTC +01:00 and in the winter UTC +00:00. But that depends if knowing if DST is in effect is important (it might not be).

Note, however that datetime2 is a smaller datasize than a datetimeoffset, so you do need to take that in considering when working with much larger result sets. If you look at Date and Time data types you'll note that datetime2 uses 6-8 bytes however, datetimeoffset uses 8-10 bytes (so the smallest size datetimeoffset is as large as the largest datetime2).

DateTime vs DateTimeOffset

DateTimeOffset is a representation of instantaneous time (also known as absolute time). By that, I mean a moment in time that is universal for everyone (not accounting for leap seconds, or the relativistic effects of time dilation). Another way to represent instantaneous time is with a DateTime where .Kind is DateTimeKind.Utc.

This is distinct from calendar time (also known as civil time), which is a position on someone's calendar, and there are many different calendars all over the globe. We call these calendars time zones. Calendar time is represented by a DateTime where .Kind is DateTimeKind.Unspecified, or DateTimeKind.Local. And .Local is only meaningful in scenarios where you have an implied understanding of where the computer that is using the result is positioned. (For example, a user's workstation)

So then, why DateTimeOffset instead of a UTC DateTime? It's all about perspective. Let's use an analogy - we'll pretend to be photographers.

Imagine you are standing on a calendar timeline, pointing a camera at a person on the instantaneous timeline laid out in front of you. You line up your camera according to the rules of your timezone - which change periodically due to daylight saving time, or due to other changes to the legal definition of your time zone. (You don't have a steady hand, so your camera is shaky.)

The person standing in the photo would see the angle at which your camera came from. If others were taking pictures, they could be from different angles. This is what the Offset part of the DateTimeOffset represents.

So if you label your camera "Eastern Time", sometimes you are pointing from -5, and sometimes you are pointing from -4. There are cameras all over the world, all labeled different things, and all pointing at the same instantaneous timeline from different angles. Some of them are right next to (or on top of) each other, so just knowing the offset isn't enough to determine which timezone the time is related to.

And what about UTC? Well, it's the one camera out there that is guaranteed to have a steady hand. It's on a tripod, firmly anchored into the ground. It's not going anywhere. We call its angle of perspective the zero offset.

Instantaneous Time vs Calendar Time Visualization

So - what does this analogy tell us? It provides some intuitive guidelines-

If you are representing time relative to some place in particular, represent it in calendar time with a DateTime. Just be sure you don't ever confuse one calendar with another. Unspecified should be your assumption. Local is only useful coming from DateTime.Now. For example, I might get DateTime.Now and save it in a database - but when I retrieve it, I have to assume that it is Unspecified. I can't rely that my local calendar is the same calendar that it was originally taken from.
If you must always be certain of the moment, make sure you are representing instantaneous time. Use DateTimeOffset to enforce it, or use UTC DateTime by convention.
If you need to track a moment of instantaneous time, but you want to also know "What time did the user think it was on their local calendar?" - then you must use a DateTimeOffset. This is very important for timekeeping systems, for example - both for technical and legal concerns.
If you ever need to modify a previously recorded DateTimeOffset - you don't have enough information in the offset alone to ensure that the new offset is still relevant for the user. You must also store a timezone identifier (think - I need the name of that camera so I can take a new picture even if the position has changed).
It should also be pointed out that Noda Time has a representation called ZonedDateTime for this, while the .Net base class library does not have anything similar. You would need to store both a DateTimeOffset and a TimeZoneInfo.Id value.
Occasionally, you will want to represent a calendar time that is local to "whomever is looking at it". For example, when defining what today means. Today is always midnight to midnight, but these represent a near-infinite number of overlapping ranges on the instantaneous timeline. (In practice we have a finite number of timezones, but you can express offsets down to the tick) So in these situations, make sure you understand how to either limit the "who's asking?" question down to a single time zone, or deal with translating them back to instantaneous time as appropriate.

Here are a few other little bits about DateTimeOffset that back up this analogy, and some tips for keeping it straight:

If you compare two DateTimeOffset values, they are first normalized to zero offset before comparing. In other words, 2012-01-01T00:00:00+00:00 and 2012-01-01T02:00:00+02:00 refer to the same instantaneous moment, and are therefore equivalent.
If you are doing any unit testing and need to be certain of the offset, test both the DateTimeOffset value, and the .Offset property separately.
There is a one-way implicit conversion built in to the .Net framework that lets you pass a DateTime into any DateTimeOffset parameter or variable. When doing so, the .Kind matters. If you pass a UTC kind, it will carry in with a zero offset, but if you pass either .Local or .Unspecified, it will assume to be local. The framework is basically saying, "Well, you asked me to convert calendar time to instantaneous time, but I have no idea where this came from, so I'm just going to use the local calendar." This is a huge gotcha if you load up an unspecified DateTime on a computer with a different timezone. (IMHO - that should throw an exception - but it doesn't.)

Shameless Plug:

Many people have shared with me that they find this analogy extremely valuable, so I included it in my Pluralsight course, Date and Time Fundamentals. You'll find a step-by-step walkthrough of the camera analogy in the second module, "Context Matters", in the clip titled "Calendar Time vs. Instantaneous Time".

datetimeoffset vs datetime2 for UTC on SQL Server

The datetimeoffset data type will allow comparison between different offsets of the same time. e.g.:

SELECT 'equal'
WHERE
    CAST('2021-02-12 15:48:11.0677934 -01:00' AS datetimeoffset) = CAST('2021-02-12 16:48:11.0677934 +00:00' AS datetimeoffset).

If you are storing only UTC values (where the offset is always zero), you can save storage space with datetime2. datetimeoffset requires 10 bytes of storage whereas datetime needs 8 bytes for precision 5 or greater, 7 bytes for precision 3-4, and 6 bytes for precision 2 or less.

Best practices with saving datetime & timezone info in database when data is dependant on datetime

Hugo's answer is mostly correct, but I'll add a few key points:

When you're storing the customer's time zone, do NOT store a numerical offset. As others have pointed out, the offset from UTC is only for a single point in time, and can easily change for DST and for other reasons. Instead, you should store a time zone identifier, preferably an IANA time zone identifier as a string, such as "America/Los_Angeles". Read more in the timezone tag wiki.
Your OrderDateTime field should absolutely represent the time in UTC. However, depending on your database platform, you have several choices for how to store this.
- For example, if using Microsoft SQL Server, a good approach is to store the local time in a datetimeoffset column, which preserves the offset from UTC. Note that any index you create on that column will be based on the UTC equivalent, so you will get good query performance when doing your range query.
- If using other database platforms, you may instead wish to store the UTC value in a timestamp field. Some databases also have timestamp with time zone, but understand that it doesn't mean it stores the time zone or offset, it just means that it can do conversions for you implicitly as you store and retrieve values. If you intend to always represent UTC, then often timestamp (without time zone) or just datetime is more appropriate.
Since either of the above methods will store a UTC time, you'll also need to consider how to perform operations that need an index of local time values. For example, you might need to create a daily report, based on the day of the user's time zone. For that, you'd need to group by the local date. If you try to compute that at query time from your UTC value, you'll end up scanning the entire table.
A good approach to deal with this is to create a separate column for the local date (or perhaps even the local datetime depending on your needs, but not a datetimeoffset or timestamp). This could be a completely isolated column that you populate separately, or it could be a computed/calculated column based on your other column. Use this column in an index so you can filter or group by local date.
If you go for the computed-column approach, you'll need to know how to convert between time zones in the database. Some databases have a convert_tz function built-in that understands IANA time zone identifiers.
If you're using Microsoft SQL Server, you can use the new AT TIME ZONE function in SQL 2016 and Azure SQL DB, but that only works with Microsoft time zone identifiers. To use IANA time zone identifiers, you'll need a third party solution, such as my SQL Server Time Zone Support project.
At query time, avoid using the BETWEEN statement. It is fully inclusive. It works ok for whole dates, but when you have time involved you're better off doing a half-open range query, such as:
```
... WHERE OrderDateTime >= @t1 AND OrderDateTime < @t2
```
For example, if @t1 were the start of today, @t2 would be the start of tomorrow.

Regarding the scenario discussed in comments where the user's time zone has changed:

If you choose to calculate the local date in the database, the only scenario you need to worry about is if a location or business switches time zones without a "zone split" occurring. A zone split is when a new time zone identifier is introduced which covers the area that changed, including their old and new rules.
For example, the latest zone added to the IANA tzdb at the time of writing this is America/Punta_Arenas, which was a zone split when the southern part of Chile decided to stay at UTC-3 when the rest of Chile (America/Santiago) went back to UTC-4 at the end of DST.
However, if a minor locality on the border of two time zones decides to change which side they follow, and a zone split wasn't warranted, then you'd potentially be using the rules of their new time zone against their old data.
If you store the local date separately (computed in the application, not the DB), then you'll have no problems. The user changes their time zone to the new one, all old data is still intact, and new data is stored with the new time zone.

Is it always a good idea to store time in UTC or is this the case where storing in local time is better?

I think that in order to answer that question, we should think about the benefits of using UTC to store timestamps.

I personally think that the main benefit to that is that the time is always (mostly) guaranteed to be consistent. In other words, whenever the timezone is changed, or DST applied, you don't get back or forth in time. This is especially useful in filesystems, logs and so on. But is it necessary in your application?

Think of two things. Firstly, about the time of DST clock shift. Is it likely that your events are going to occur between 2 AM and 3 AM (on the day the clock shift is done)? What should happen then?

Secondly, will the application be subject to actual timezone changes? In other words, are you going to fly with it from London to Warsaw, and change your computer timezone appropriately? What should happen in that case?

If you answered no to both of those questions, then you're better with the local time. It will make your application simpler. But if you answered yes at least once, then I think you should give it more thinking.

And that was all about the database. The other thing is the time format used internally by the application, and that should depend on what actually you will be doing with that time.

You mentioned it exposing the time via an API. Will the application query the database on every request? If you store the time internally as UTC, you will either need to do that or otherwise ensure that on DST/timezone change the cached times will be adjusted/pruned.

Will it do anything with the time itself? Like printing the event will occur in 8 hours or suspending itself for circa that time? If yes, then UTC will probably be better. Of course, you need to think of all the forementioned issues.