Zero date offset sql server. Removing time zone offset from DateTimeOffset

The humane option ([you must register to view the link]).

The essence of the problem is this.. If you accidentally deployed a database on a SQL server with a “date offset” of 0, then a problem arises when the database contains an attribute with the TIME type, i.e. this attribute is set to 01/01/0001 10:30:00 or date registered empty 01.01.0001 00:00:00. When recording such details, it is not recorded.
On the Internet they offer to create new base 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 followed the path of where is this value stored in SQL. I found it and changed it to 2000 and everything was ok..
And now step by step where to change.

Kick all users.

ATTENTION!!!

1. Do it first backup copy by means of 1C i.e. upload it to *.dt
This must be done before changing the “offset”
If this is not done, then in your entire database there will be references, docs, etc.
where is there is a requisite the date will be say 02.10.0009
WHAT IS NOT ALLOWED...
So you have uploaded to *.dt

2. Go to SQL Server Management Studio
Find your base in the list and 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, stand on it and select the “Open table” item with the right button, 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.
The result is that we changed the “date offset from 0 to 2000”

Sometimes it happens that this option cannot be used for one reason or another. Then there is a more hardcore option ([you must register to view the 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
deallocateTablesAndFields
go

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

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 enable -4:00 to +00:00 (from code before saving)?

I have 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 to simply have a date with no time or timezone 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 the 00:00:00.0000000 time and 00:00:00.0000000 offset the set time by +00:00 , I just I 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

• 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 applies the local time zone. In your case, it appears that the offset for this value in your local timezone 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 to simply have a date with no time or timezone 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 it's not quite ready for a typical production application. There's LocalDate in Noda's time library, which you can use today, but you'll still have to convert back to a native type before storing it in the database. So in the meantime, the best you can do is:

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

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

I want the current date with zero offset.

If you really want it like DateTimeOffset you would do:

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

However, I advise against this. By doing this you are taking the local date of the original value and asserting that it is in UTC. If the original offset is anything other than zero, this will be a false statement. It will subsequently lead to other 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 view I gave above. I still recommend against this for the same reasons.

Let's look at the example of starting from 2016-12-31T22:00:00-04:00. According to your approach you should save 2016-12-31T00:00:00+00:00 in the database. However, these are two different points in time. The first, normalized to UTC, would be 2017-01-01T02:00:00+00:00, and the second, converted to another time zone, would be 2016-12-30T20:00:00-04:00. Please note the change in dates in the conversion. This is probably not behavior you would want in your application.

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

TestDateAndTime = testDateAndTime.DateTime.Date;

Let's break it down:

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

You said:

The ultimate goal is to simply have a date with no time or timezone offset.

Well, there is currently not a native C# data type, which is just a date without a 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'll 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 date type in the field.
• In your .NET code, use DateTime with time 00:00:00 and DateTimeKind.Unspecified. You have to remember to ignore the time part (since there are indeed dates without local midnight in certain time zones).
• Change the test prop to be a DateTime rather than a DateTimeOffset.

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

I want the 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 don't recommend doing this. By doing this you take local the date of the original value and claim that it is in UTC. If the original offset is anything other than zero, this will be a false statement. It will subsequently lead to other 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 posed to 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 view I gave above. I still recommend against this for the same reasons.

Consider an example starting with 2016-12-31T22:00:00-04:00. According to your approach you should save 2016-12-31T00:00:00+00:00 in the database. However, these are two different points in time. The first, normalized to UTC, would be 2017-01-01T02:00:00+00:00, and the second, converted to another time zone, would be 2016-12-30T20:00:00-04:00. Please note the change in dates in the conversion. This is probably not behavior you would want in your application.

Almost all projects encounter problems caused by improper handling and storage of date and time. Even if the project is used in the same 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 so.

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

Let's consider each point separately, not forgetting about.

date and time

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

Let's consider one of the possible chains of actions through which data passes from the client to the server and back, which allows you to always correctly display the date/time according to the client's current time zone:

1. The value is created on the client, for example March 2, 2016 15 :13:36, the client is in time zone +2.
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, bringing 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 the data to the database, but 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, March 2, 2016 will be saved to the database 14 :13:36 in an “unknown” time zone.
6. The server reads data from the database and creates a corresponding object with the value March 2, 2016 14 :13:36. And since the server operates in time zone +1, 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 value displayed should be March 2, 2016 09 :13:36.

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

• The time on the client can be generated 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 into 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 generated 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 proofreading.
• If application servers working with a common database are located 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 lead to incorrect interpretation of already stored date/time values.

But the most serious drawback in the chain described above is the use of a local time zone on the server. If there is no changeover to summer/winter time, then no additional problems will not be. But otherwise, you can get a lot of unpleasant surprises.

The rules for converting to summer/winter time are, strictly speaking, variable. Different countries may change their rules from time to time, and these changes should be built into system updates well in advance. In practice, situations have repeatedly arisen incorrect operation of this mechanism, which were ultimately resolved by installing hotfixes or operating system, or used third-party libraries. The likelihood of the same problems repeating is not zero, so it is better to have a way to ensure that they are avoided.

Taking into account the considerations described above, we will formulate the most reliable and simple approach to transmitting and storing time: on the server and in the database, all values ​​must be converted to the UTC time zone.

Let's look at 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 conversion, but the first option is more flexible. When receiving data back from the server, the client will convert the date and time to its local time zone, knowing that in any case it will receive the time in UTC.
• There are no transitions between summer and winter time in UTC, so the problems associated with this will not be relevant.
• On the server, when reading from the database, you do not need to convert time values; you just need to explicitly indicate that it corresponds to UTC. In .NET, for example, this can be achieved by setting the DateTimeKind on 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 in any way affect the correctness of the received data.

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 back.
2. Ensure that the server-side deserializer creates date/time objects in UTC.
3. Make sure that when reading from the database, date/time objects are created in UTC. This item is sometimes provided without code changes - simply the system time zone on all servers is set to UTC.

The above considerations and recommendations work great when two conditions are combined:

• The system requirements do not require that the local time and/or time zone offset be displayed exactly as it was stored. For example, airline tickets must print departure and arrival times in the time zone corresponding to the airport location. Or if the server sends printing invoices created in different countries, each should end up with local time, and not converted to the server 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 vehicle took place at 23:00 Kyiv time,” or “the meeting will take place from 13:30 to 14:30 Minsk time.” The numbers for these events will be different in different time zones, 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 television program will air from 9:00 to 10:00 am in every country where there is a TV channel affiliate.” It turns out that the broadcast of a program is not one event, but several, and potentially all of them can occur at different periods of time on an “absolute” scale.

For cases where the first condition is violated, the problem can be solved by 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 DBMSs.

.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 complex 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 occurred or will occur for a given time zone, it is enough to simply disable time conversion. For example, the user entered the start of the program for all branches of the television 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 program (send out notifications or check the presence of some data in the TV company’s database). Implementing such a scheduler reliably is not a trivial task. Let's say the scheduler knows what time zone each branch is in. And one of the countries where there is a branch decides to change the time zone after some time. The case is not as rare as it might seem - over this and the two previous years I counted more than 10 similar events (http://www.timeanddate.com/news/time/). It turns out that either users must keep time zone bindings up to date, or the planner must automatically take this information from global sources such as the Google Maps Time Zone API. I do not undertake to offer a universal solution for such cases, I will 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 above-mentioned situations will occur in your system. Most likely, everything will be limited to the first proposed approach with storage in UTC. Well, the exceptional situations described do not cancel it, but simply add other solutions for special cases.

Date without time

Let's say with correct display dates and times, taking into account the client’s time zone, were sorted out. 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 “regular” dates and times?

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” type. Even if only a date was specified when creating such an object, the time is still present and it is equal to 00:00:00. If we transfer the value “February 2, 2016 00:00:00” from a zone with an offset of +2 to +1, we get “February 1, 2016 23:00:00”. For the example above, this would be equivalent to the new contract starting on February 2 in one time zone, and on February 1 in the other. 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 attribute to the object metadata that will tell the serializer that for given value time zone should be ignored.
• 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 based on client regional settings. It has even more disadvantages than the previous option - for example, if parts of the date in the stored string are not in the order “year, month, day,” then it will be impossible to do an effective indexed search by date range.

You can, of course, try to give a counterexample and say that the contract only makes sense within the country in which it is concluded, the country is in the same time zone, and therefore the moment it comes into force can be unambiguously determined. 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 moment in time, then it would have 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 time units (integer or floating point, depending on the precision required). If second accuracy is important, then as the number of seconds, if millisecond accuracy is important, then as the number of milliseconds, etc.

But calculating the interval can have pitfalls. Let's say we have some sample C# code that calculates 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 this is not so. 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 initial moment of time fell on winter time, and the final moment fell on summer time (for example, this is how the number of working hours is measured, and workers have a night shift).

Let's assume the code is running in a time zone in which daylight saving 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, although in fact 8 hours have passed between these moments. You can easily verify this 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 You need to deal with date and time using either UTC values ​​or types that store time zone information. And then transfer back to local ones if necessary. From this point of view, the original example can be easily corrected by changing DateTime.Now to DateTime.UtcNow.

This nuance does not depend on a specific platform or language. Here is similar code in Java that has the same problem:

LocalDateTime start = LocalDateTime.now(); //... LocalDateTime end = LocalDateTime.now(); long hours = ChronoUnit.HOURS.between(start, end);
It can also be easily fixed - for example, by using ZonedDateTime instead of LocalDateTime.

Schedule of scheduled events

Scheduling planned events is a more complex situation. There is no universal type that allows you to store schedules in standard libraries. But such a task does not arise very rarely, 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 options like the “second Friday of the month.”

In most cases, it doesn’t make sense to write your own scheduler, 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 schedule format can be borrowed from cron.

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

Firstly, regarding the use of static class members to obtain the current time - DateTime.UtcNow, ZonedDateTime.now(), etc. As was said, using them directly in code can seriously complicate unit testing, since without special mocking frameworks it can be replaced current time will not work. Therefore, if you plan to write unit tests, you should make sure that the implementation of such methods can be replaced. There are at least two ways to solve this problem:

• Provide an 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 from a testing point of view.
• Make your own static class with a method for getting the current time and the ability to install 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) method impl). Usage static property or a method for obtaining the current time eliminates the need to explicitly specify a dependency on an additional interface everywhere, but from the point of view of OOP principles it is not an ideal solution. However, if for some reason the previous option is not suitable, then you can use this one.

An additional issue that should be addressed when migrating to your current time provider implementation is making sure that no one continues to use standard classes the “old fashioned way.” This task is easy to solve in most code quality control systems. Essentially, it comes down to searching for an “unwanted” substring in all files except the one where the “default” implementation is declared.

The second caveat to getting the current time is that the 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 obtain the current time should, if possible, be done 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 date and time used in serialization must conform to this standard to prevent potential compatibility issues. In practice, it is extremely rare that you have to implement the formatting yourself, so the standard itself can be useful mainly for informational purposes.

Tags: Add tags