Almost all projects face problems caused by improper processing and storage of dates and times. Even if the project is used in one time zone, you can still get unpleasant surprises after switching to winter / summer time. At the same time, few people are puzzled by the implementation of the correct mechanism from the start, because it seems that there can be no problems with this, since everything is trivial. Unfortunately, later reality shows that this is not the case.

Logically, the following types of values ​​related to date and time can be distinguished:

Consider each item separately, not forgetting about.

date and time

Suppose the laboratory that collected the material for analysis is in the +2 time zone, and the central branch, which monitors the timely completion of analyzes, is in the +1 time zone. The time given in the example was noted when the material was collected by the first laboratory. The question arises - what figure of time should the central office see? Obviously the software central office should show January 15, 2014 12:17:15 pm - an hour less, since according to their clock the event occurred at that very moment.

Consider one of the possible chains of actions through which data passes from the client to the server and vice versa, which allows you to always correctly display the date / time according to the current time zone of the client:

1. The value is created on the client, for example March 2, 2016 15 :13:36, the client is in the +2 time zone.
2. The value is converted to a string representation for transmission to the server - “2016-03-02T 15 :13:36+02:00”.
3. The serialized data is sent to the server.
4. The server deserializes the time into a date/time object, casting it to its current time zone. For example, if the server is running at +1, then the object will contain March 2, 2016 14 :13:36.
5. The server saves data to the database, while it does not contain any information about the time zone - the most commonly used date / time types simply do not know anything about it. Thus, the database will be saved March 2, 2016 14 :13:36 in an "unknown" time zone.
6. The server reads the data from the database and creates the corresponding object with the value March 2, 2016 14 :13:36. And since the server works in the +1 time zone, this value will also be interpreted within the same time zone.
7. The value is converted to a string representation for transmission to the client - “2016-03-02T 14 :13:36+01:00”.
8. The serialized data is sent to the client.
9. The client deserializes the received value into a date/time object, casting it to its current time zone. For example, if it is -5, then the displayed value should be March 2, 2016 09 :13:36.

Everything seems to be holistic, but let's think about what can go wrong in this process. In fact, problems here can happen at almost every step.

• The time on the client can be formed without a time zone at all - for example, the DateTime type in .NET with DateTimeKind.Unspecified.
• The serialization engine may use a format that does not include a time zone offset.
• When deserializing to an object, the time zone offset can be ignored, especially in "homemade" deserializers - both on the server and on the client.
• When reading from a database, a date/time object can be formed without a time zone at all - for example, the DateTime type in .NET with DateTimeKind.Unspecified. Moreover, with DateTime in .NET, in practice, this is exactly what happens if you do not explicitly specify another DateTimeKind immediately after the subtraction.
• If the application servers working with a common database are in different time zones, there will be serious confusion in time offsets. The date/time value written to the database by server A and read by server B will be noticeably different from the same original value written by server B and read by server A.
• Transferring application servers from one zone to another will misinterpret already stored date/time values.

But the most serious drawback in the chain described above is the use of the local time zone on the server. If it does not have daylight saving time, then there will be no additional problems. But otherwise, you can get a lot of unpleasant surprises.

The rules for transferring to summer / winter time are, strictly speaking, a variable thing. Different countries may change their rules from time to time, and these changes should be incorporated into system updates well in advance. In practice, there have been numerous situations of incorrect operation of this mechanism, which, as a result, were solved by installing hotfixes or operating system, or third-party libraries used. The probability of repeating the same problems is not zero, so it is better to have a way to avoid them with guarantee.

Considering the considerations described above, let's formulate the most reliable and simple approach to the transfer and storage of time: on the server and in the database, all values ​​must be converted to the UTC time zone.

Consider what this rule gives us:

• When sending data to the server, the client must pass the time zone offset so that the server can correctly convert the time to UTC. An alternative option is to force the client to do this transformation, but the first option is more flexible. When receiving data back from the server, the client will translate the date and time into its local time zone, knowing that it will arrive in UTC anyway.
• In UTC there are no transitions to summer and winter time, respectively, the problems associated with this will be irrelevant.
• On the server, when reading from the database, you do not need to convert the time values, it is enough to explicitly indicate that it corresponds to UTC. In .NET, for example, this can be achieved by setting the DateTimeKind of the time object to DateTimeKind.Utc.
• The difference in time zones between servers working with a common database, as well as the transfer of servers from one zone to another, will not affect the correctness of the data received.

To implement such a rule, it is enough to take care of three things:

1. Make the serialization and deserialization mechanism such that date/time values ​​are correctly translated from UTC to the local time zone and vice versa.
2. Make sure the server side deserializer creates date/time objects in UTC.
3. Make it so that when subtracting from the database, date / time objects are created in UTC. This item is sometimes provided without code changes - just the system time zone on all servers is set to UTC.

The above considerations and recommendations work great with a combination of two conditions:

• There is no need in the system requirements to display the local time and/or time zone offset exactly as it was stored. For example, in airline tickets, the time of departure and arrival must be printed in the time zone corresponding to the location of the airport. Or if the server prints invoices created in different countries, each should end up with local time, not converted to the server's time zone.
• All date and time values ​​in the system are "absolute"—i.e. describe a point in time in the future or past that has a single value in UTC. For example, "the launch of the launch vehicle took place at 23:00 Kiev time", or "the meeting will run from 13:30 to 14:30 Minsk time." In different time zones, the numbers for these events will be different, but they will describe the same point in time. But it may happen that the requirements for software imply "relative" local time for some cases. For example, "this TV show will run from 9:00 am to 10:00 am in every country where there is a branch of the TV channel." It turns out that the show of the program is not one event, but several, and potentially all of them can occur in different periods of time according to the “absolute” scale.

For cases where the first condition is violated, the problem can be solved using data types containing the time zone - both on the server and in the database. Below is a small list of examples for different platforms and DBMS.

.NET	datetimeoffset
Java	org.joda.time.DateTime, java.time.ZonedDateTime
MS SQL	datetimeoffset
Oracle, PostgreSQL	TIMESTAMP WITH TIME ZONE
MySQL	—

Violation of the second condition is a more complicated case. If this “relative” time needs to be stored simply for display, and there is no task to determine the “absolute” moment in time when the event has occurred or will occur for a given time zone, it is enough to simply disable time conversion. For example, the user entered the beginning of the transmission for all branches of the TV company on March 25, 2016 at 9:00, and it will be transmitted, stored and displayed in this form. But it may happen that some scheduler should automatically perform special actions an hour before the start of each transmission (send notifications or check for the presence of some data in the broadcaster's database). Reliable implementation of such a scheduler is not a trivial task. Let's say the scheduler knows what time zone each of the branches is in. And one of the countries where there is a branch decides to change the time zone after a while. The case is not as rare as it might seem - in this and the two previous years, I counted more than 10 such events (http://www.timeanddate.com/news/time/). It turns out that either users must keep bindings to time zones up to date, or the scheduler must automatically take this information from global sources such as Google Maps Timezone API. I do not undertake to offer a universal solution for such cases, I simply note that such situations require serious study.

As can be seen from the above, there is no single approach that covers 100% of cases. Therefore, you first need to clearly understand from the requirements which of the situations mentioned above will be in your system. With a high probability, everything will be limited to the first proposed approach with storage in UTC. Well, the described exceptional situations do not cancel it, but simply add other solutions for special cases.

date without time

Let's say we figured out the correct display of the date and time, taking into account the client's time zone. Let's move on to dates without time and the example given for this case at the beginning - "the new contract comes into force on February 2, 2016". What will happen if the same types and the same mechanism are used for such values ​​as for "ordinary" dates with time?

Not all platforms, languages, and DBMSs have date-only types. For example, in .NET there is only the DateTime type, there is no separate "just Date". Even if only a date was specified when creating such an object, the time is still present and equals 00:00:00. If we translate the value “February 2, 2016 00:00:00” from the zone with an offset of +2 into +1, then we get “February 1, 2016 23:00:00”. For the example above, this would be equivalent to the fact that in one time zone the new contract will begin on February 2, and in the other on February 1. From a legal point of view, this is absurd and, of course, it should not be so. General rule for “pure” dates, it is extremely simple - such values ​​should not be converted at any step of saving and reading.

There are several ways to avoid conversion for dates:

• If the platform supports a type that represents a date without a time, then that should be used.
• Add a special feature to the object metadata that will tell the serializer that the time zone should be ignored for the given value.
• Pass the date from the client and back as a string, and store it as a date. This approach is inconvenient if you need to not only display the date on the client, but also perform some operations on it: comparison, subtraction, etc.
• Pass and store as a string, and convert to a date only for formatting, taking into account the client's regional settings. It has even more disadvantages than the previous version - for example, if the date parts in the stored string are not in the order “year, month, day”, then it will be impossible to make an efficient indexed search by date range.

You can, of course, try to give a counterexample and say that the contract makes sense only within the country in which it is concluded, the country is in the same time zone, and therefore it is possible to unambiguously determine the moment of its entry into force. But even in this case, users from other time zones will not be interested in what moment in their local time this event will occur. And even if there was a need to show this point in time, then it would be necessary to display not only the date, but also the time, which contradicts the original condition.

Time interval

With the storage and processing of time intervals, everything is simple: their value does not depend on the time zone, so there are no special recommendations here. They can be stored and transmitted as a number of units of time (integer or floating point, depending on the required precision). If second accuracy is important, then as the number of seconds, if millisecond accuracy, then as the number of milliseconds, etc.

But the calculation of the interval can have pitfalls. Let's say we have some generic C# code that counts the time interval between two events:

DateTime start = DateTime.Now; //... DateTime end = DateTime.Now; double hours = (end - start).TotalHours;
At first glance, there are no problems here, but it is not. Firstly, there may be problems with unit testing such code, but we will talk about this a little later. Secondly, let's imagine that the start time is winter time and the end time is summer time (for example, the number of working hours is measured in this way, and workers have a night shift).

Assume the code is running in a time zone where daylight savings time in 2016 occurs on the night of March 27, and simulate the situation described above:

DateTime start = DateTime.Parse("2016-03-26T20:00:15+02"); DateTime end = DateTime.Parse("2016-03-27T05:00:15+03"); double hours = (end - start).TotalHours;
This code will result in 9 hours, even though 8 hours have actually elapsed between these times. This can be easily verified by changing the code like this:

DateTime start = DateTime.Parse("2016-03-26T20:00:15+02").ToUniversalTime(); DateTime end = DateTime.Parse("2016-03-27T05:00:15+03").ToUniversalTime(); double hours = (end - start).TotalHours;
Hence the conclusion - any arithmetic operations with date and time must be done using either UTC values ​​or types that store information about the time zone. And then back to translate into local if necessary. From this point of view, the original example is easy to fix by changing DateTime.Now to DateTime.UtcNow.

This nuance does not depend on a specific platform or language. Here is a similar Java code with the same drawback:

LocalDateTime start = LocalDateTime.now(); //... LocalDateTime end = LocalDateTime.now(); long hours = ChronoUnit.HOURS.between(start, end);
It is also easy to fix - for example, using ZonedDateTime instead of LocalDateTime.

Schedule of scheduled events

The scheduling of scheduled events is a more complex situation. There is no generic type that allows you to store schedules in the standard libraries. But such a task is not so rare, so ready-made solutions can be found without problems. A good example is the cron scheduler format, which is used in one form or another by other solutions, such as Quartz: http://quartz-scheduler.org/api/2.2.0/org/quartz/CronExpression.html . It covers almost all scheduling needs, including "second Friday of the month" options.

In most cases, writing your own scheduler does not make sense, since there are flexible time-tested solutions, but if for some reason there is a need to create your own mechanism, then at least the scheduling format can be borrowed from cron.

In addition to the recommendations described above on the storage and processing of different types of time values, there are several others that I would also like to talk about.

First, about using static class members to get the current time - DateTime.UtcNow, ZonedDateTime.now(), etc. As mentioned, using them directly in the code can seriously complicate unit testing, since without special mock frameworks it will not be possible to change the current time. Therefore, if you plan to write unit tests, you should take care that the implementation of such methods can be replaced. There are at least two ways to solve this problem:

• Dedicate the IDateTimeProvider interface with a single method that returns the current time. Then add a dependency to this interface in all code units where you need to get the current time. During normal program execution, the default implementation will be injected into all these places, which returns the real current time, and in unit tests - any other necessary implementation. This method is the most flexible in terms of testing.
• Make your own static class with a method for getting the current time and the ability to set any implementation of this method from the outside. For example, in the case of C# code, this class can expose the UtcNow property and the SetImplementation(Func impl). Using a static property or method to get the current time eliminates the need to explicitly specify the dependence on an additional interface everywhere, but from the point of view of OOP principles, this is not an ideal solution. However, if for some reason the previous option is not suitable, then you can use this one.

An additional problem that needs to be solved when migrating to your own implementation of the current time provider is making sure that no one "the old fashioned way" continues to use the standard classes. This task is easy to solve in most code quality control systems. In essence, it boils down to searching for the “unwanted” substring in all files except for those where the “default” implementation is declared.

The second nuance with getting the current time is that client cannot be trusted. The current time on users' computers can be very different from the real one, and if there is logic tied to it, then this difference can ruin everything. All places where there is a need to get the current time should, if possible, be performed on the server side. And, as mentioned earlier, all arithmetic operations with time must be performed either in UTC values, or using types that store the time zone offset.

And one more thing that I wanted to mention is the ISO 8601 standard, which describes the date and time format for information exchange. In particular, the string representation of a date and time used in serialization must conform to this standard to prevent potential compatibility issues. In practice, it is extremely rare to have to implement formatting yourself, so the standard itself can be useful mainly for informational purposes.

Tags: Add tags

Humane option ([you need to register to view this link]).

The essence of the problem is .. If you accidentally deployed the database on the SQL server with a “date offset” of 0, then there was a problem when an attribute with the TIME type is found in the database, i.e. this attribute is set to 01.01.0001 10:30:00 or the date signed up empty 01.01.0001 00:00:00. When such attribute is recorded, it is not recorded.
On the Internet, they suggest creating a new database with an offset of 2000.
But I didn't really want to create a new base. And change the path to the database for all users.
Then I went along the path, but where is this value stored in SQL. Found and changed to 2000 and everything was ok ..
And now, step by step, where to change.

Kick out all users.

ATTENTION!!!

1. First do backup by means of 1C i.e. upload it to *.dt
This must be done before you change the "offset"
If this is not done, then in your entire database, ref., doc, etc.
where there is a props the date will be let's say 02.10.0009
WHAT IS NOT ALLOWED….
So you have made an upload to *.dt

2. Go to SQL Server Management Studio
We find your base in the list, press the plus sign.
Find the "Tables" folder there and open it.
A bunch of tables will open, go to the very bottom, find the table
_YearOffset, we stand on it and right-click on the item “Open table”, see Fig. 1
Change the value 0 to 2000
Close SQL Server Management Studio

3. Go to the configurator and load the previously saved database.

If this is not done, then all dates will be with the year 0009.
After the database has loaded ... You can go to 1C and make sure that the dates are normal.
As a result, we changed the "offset date from 0 to 2000"

Sometimes it happens that this option cannot be used for one reason or another. Then there's a more hardcore version ([You need to register to view this link]):

Declare TablesAndFields cursor for

SELECT objects.name as Tablename, columns.name as columnname
FROM dbo.sysobjects as objects
left join dbo.syscolumns as columns on objects.id = columns.id
where objects.xtype = "U" and columns.xtype = 61

open TablesAndFields

WHILE @@FETCH_STATUS = 0
BEGIN Exec(" update"+ @TableName + "
set" + @ColumnName + " = ""2000- 01- 01 00:00:00" "
where" + @ColumnName + " > ""3999- 12- 31 23:59:59" "")

This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM TablesAndFields into @TableName, @ColumnName
END

close TablesAndFields
deallocate TablesAndFields
go

Before any manipulations, do not forget to make copies of the databases!

The problem has nothing to do with the database. If you set a breakpoint or enter an exit somewhere, you should be able to see the offset being clipped shortly after this code:

TestDateAndTime = testDateAndTime.DateTime.Date;

Let's break it 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 are assigning the result to testDateAndTime , which is of type DateTimeOffset . This calls an implicit cast from DateTime to DateTimeOffset - which applies local Timezone. In your case, the offset for that value in your local time zone appears to be -04:00 , so the resulting value is a DateTimeOffset of 2008-05-01T00:00:00-04:00 as you described.

You said:

The end goal is to simply have a date with no time or time zone offset.

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

• Change your SQL Server to use the date type on the field.
• In your .NET code, use DateTime with time 00:00:00 and DateTimeKind.Unspecified . You must remember to ignore the time part (since there are valid dates without local midnight in certain time zones).
• Change test props to be DateTime , not DateTimeOffset .

In general, while DateTimeOffset is suitable for a large number of scenarios (for example, timestamps events), it doesn't fit well for date-only values.

I want to current date with zero offset.

If you really want it's like DateTimeOffset , you could do:

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

However, I do not recommend doing this. By doing so, you take local the date of the original value and claim that it is in UTC. If the original offset is anything but zero, this will be a false statement. It will subsequently lead to different errors, since you are 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 board. Specifying DateTimeKind.Utc changes the behavior of the implicit cast. Instead of using the local time zone, UTC time is used, which always has a zero offset. The result is the same as the more explicit look I gave above. I still recommend against it for the same reasons.

Consider an example starting with 2016-12-31T22:00:00-04:00 . According to your approach, you should store 2016-12-31T00:00:00+00:00 in the database. However, these are two different times. The first one, normalized to UTC, will be 2017-01-01T02:00:00+00:00 , and the second one, converted to another timezone, will be 2016-12-30T20:00:00-04:00 . Pay attention to the date change in the conversion. This is probably not the behavior you would like to use in your application.

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();

RESULT IN DATABASE: 2008-05-01 00:00:00.0000000 -04:00

How to turn -4:00 into +00:00 (from code before saving)?

I've tried:

Public Task SetTimeZoneOffsetToZero(DateTimeOffset dateTimeOffSetObj) ( TimeSpan zeroOffsetTimeSpan = new TimeSpan(0, 0, 0, 0, 0); return dateTimeOffSetObj.ToOffset(zeroOffsetTimeSpan); )

He does not do anything.

The end goal is just to have a date with no time or time zone offset. I do NOT want to convert the time to another time zone (i.e. 00:00:00.0000000 I don't want it to subtract 4 hours from 00:00:00.0000000 time and 00:00:00.0000000 offset the set time by +00:00 , I just want it to set the offset to +00:00). I want the current date with zero offset.

Edit:

Here's what you can offer 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 be the SpecifyKind of my dateTimeOffset, change BOTH the time and timezone offset for example, but in testing it seems to just change the timezone offset, which is what I want. Is there a problem with this?

1 answer

The problem has nothing to do with the database. If you've set a breakpoint or logged an exit somewhere, you should see the offset attached shortly after this code:

TestDateAndTime = testDateAndTime.DateTime.Date;

Let's break it down:

• You started with DateTimeOffset 2008-05-01T08:06:32+01:00
• You then called .DateTime , resulting 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 return the result in testDateAndTime , which is of type DateTimeOffset . This causes an implicit conversion from DateTime to DateTimeOffset which uses the local timezone. In your case, it appears that the offset for that value in your local time zone is -04:00 , so the resulting value is DateTimeOffset 2008-05-01T00:00:00-04:00 as you described,

You said:

The end goal is just to have a date with no time or time zone offset.

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

• Change your SQL Server to use the date type in this field.
• In your .NET code, use DateTime with time 00:00:00 and DateTimeKind.Unspecified . You must remember to ignore the time part (since there are valid dates without local midnight in certain time zones).
• Change the test alert to be DateTime , not DateTimeOffset .

In general, while DateTimeOffset is suitable for a large number scenarios (e.g. timestamping events), it is not suitable for date-only values.

I want the current date with zero offset.

If you really want it as a DateTimeOffset you would do:

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

However, I advise against it. By doing so, you are taking the local date of the original value and asserting that it is in UTC. If the original offset is anything but zero, this will be a false statement. It will subsequently lead to different errors, since you are 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, UTC time is used, which always has a zero offset. The result is the same as the more explicit look I gave above. I still recommend against it for the same reasons.

Consider the example of starting from 2016-12-31T22:00:00-04:00 . According to your approach, you should store 2016-12-31T00:00:00+00:00 in the database. However, these are two different times. The first one, normalized to UTC, will be 2017-01-01T02:00:00+00:00 , and the second one, converted to another time zone, will be 2016-12-30T20:00:00-04:00 . Pay attention to the date change in the conversion. This is probably not the behavior you would like to use in your application.