Date/Time

First let's think of date/time as a point on a timeline. The following image shows the point in time when the clocks go back from British Summer Time (BST) to Coordinated Universal Time (UTC) (also known as Greenwich Mean Time).

UTC is a regular time which is not affected by daylight saving and it is the preferred format for storing date/time, you would then convert from this to display in the user's local time.

DateTime vs DateTimeOffset

These are the two .Net types for storing date/times. To describe the different we'll use an example. First we will set the user’s local time zone (TZ):

var tz = TimeZoneInfo.FindSystemTimeZoneById("GMT Standard Time");

Now set the UTC DateTime (DT):

var dtUtc = new DateTime(2018, 6, 28, 11, 30, 0, DateTimeKind.Utc);

Now to display this in the user's local TZ we would say:

TimeZoneInfo.ConvertTimeFromUtc(dtUtc, tz).ToString(); // 28/06/2018 12:30:00

If the DateTimeKind is not specified then it will be set to Unspecified. The problem now is that the same time occurs for multiple TZs and we have no information about which TZ the date is from. To overcome this we should use DateTimeOffset (DTO) to store the UTC offset along with the DT (from the UTC date). For example:

var dt = new DateTime(2018, 6, 28, 13, 30, 0);
var dto = new DateTimeOffset(dt, TimeSpan.FromHours(2));

Alternatively this can be written as:

var dto = new DateTimeOffset(2018, 6, 28, 13, 30, 0, TimeSpan.FromHours(2));

Now to display this in the user's local TZ we would say:

TimeZoneInfo.ConvertTime(dto, tz).ToString(); // 28/06/2018 12:30:00 +01:00

As you can see it's usually desirable to use DTO, however some libraries require you to pass a DT. To get the UTC DT from the DTO you say:

dto.UtcDateTime

Comparing / Adding / Subtracting

When comparing two DT's we can only really compare them if their DateTimeKind is set to UTC (since we then know they are in the same time zone). However with a DTO we can compare them regardless of the offset. For example:

new DateTimeOffset(2018, 6, 28, 12, 30, 0, TimeSpan.FromHours(1)) == new DateTimeOffset(2018, 6, 28, 13, 30, 0, TimeSpan.FromHours(2)); // True

When adding or subtracting from a DateTimeOffset it correctly handles adjustments due to daylight saving time (DST). For example on 28th October 2018 at 02:00 the clocks will be adjusted back to 01:00. Therefore if we take a time before this adjustment e.g. 28th October 2018 at 00:30 BST and add 1 day (24 hours) you would get:

var dto = new DateTimeOffset(2018, 10, 28, 0, 30, 0, TimeSpan.FromHours(1)); // 28/10/2018 00:30:00 +01:00

dto = dto.AddDays(1); // 29/10/2018 00:30:00 +01:00

Since the DTO is not aware of the TZ then it simply adjusts the base time (not accounting for the offset). Now if you were to display this in the user's TZ you would get:

TimeZoneInfo.ConvertTime(dto, tz).ToString(); // 28/10/2018 23:30:00 +00:00

Note that although we have affectively added a day it appears that we have lost an hour. This is because the time between 01:00 and 02:00 was repeated and when adjusting the DTO it uses a chronological computation and adds a fixed time span (24 hours in this case).

Sometimes this is not desirable if we want to keep the same time (known as a calendrical computation). To achieve this we should change the user's local time. For example say we want to get the GMT for the start of a month in the future where a DST adjustment happens during that time then we would want to make sure we return the time at midnight and not the hour before. To accomplish this we would do the following:

var dto = new DateTimeOffset(2018, 6, 1, 0, 0, 0, TimeSpan.FromHours(1));
new DateTimeOffset(dto.DateTime.AddMonths(6), tz.GetUtcOffset(dto.DateTime.AddMonths(6))); // 01/12/2018 00:00:00 +00:00

In KIT we have added a DTO extension method to achieve this, all you have to do is pass the TZ when calling the AddDays/Months/Years methods, for example:

dto.AddMonths(6, tz);

This has been taken from https://stackoverflow.com/a/47711599/155899, also please refer to https://stackoverflow.com/a/45035536/155899 for more information regarding chronological and calendrical computations.

Invalid and Ambiguous DateTime(s)

Due to DST there are times in a user's local time that are repeated (as you can see in the time picture above), this is known as an ambiguous time. There are also times which are impossible (when the clocks move forward an hour) which is known as an invalid time.

Given the following DT for a particular user it is impossible to tell if this was 01:30 before or after the clocks were adjusted:              

var dt = new DateTime(2018, 10, 28, 1, 30, 0);

However if we use a DTO then we can store the UTC offset and it's no longer ambiguous. For example:

var dto1 = new DateTimeOffset(dt, TimeSpan.FromHours(1)); // Before the clocks go back.
var dto2 = new DateTimeOffset(dt, TimeSpan.FromHours(0));

With an invalid date you can't create a DT as it will throw an exception.

Database

Since MS SQL Server 2008 there is a datetimeoffset type. If you store your dates using this type then you can easily filter for data. For example say I'd like to get all the articles added in June 2008 then first you would get the DTO for the start of the month for the current user:

var startOfMonth = new DateTimeOffset(new DateTime(2018, 6, 1), TimeSpan.FromHours(1));

Now add a month (using the extension method above in case the interval overlaps DST)

var startOfNextMonth = startOfMonth.AddMonths(1, tz);

Now we can filter the articles like so:

var articles = session.Query<Article>().Where(a => a.DateAdded >= startOfMonth && a.DateAdded < startOfNextMonth).ToList();