What we can do with date and time offset data
-
see here we store offset. so tell me what is the importance of storing offset data along with date and time ? what we can do with offset data if we store ? public void DemoDateTimeOffset() { DateTimeOffset offsetValue = new DateTime(2017, 1, 21, 4, 34, 0); using (SqlConnection cn = new SqlConnection(myConnection)) { string commandText = @" INSERT INTO DateTimeDemo (DateTimeExample,DateTimeOffsetExample) VALUES (@DateTimeExample,@DateTimeOffsetExample)"; using (SqlCommand cmd = new SqlCommand(commandText, cn)) { cmd.Parameters.AddWithValue("@DateTimeExample", DateTime.Now); cmd.Parameters.AddWithValue("@DateTimeOffsetExample", offsetValue); cn.Open(); cmd.ExecuteNonQuery(); } } } [screen shot] thanks
tbhattacharjee
-
see here we store offset. so tell me what is the importance of storing offset data along with date and time ? what we can do with offset data if we store ? public void DemoDateTimeOffset() { DateTimeOffset offsetValue = new DateTime(2017, 1, 21, 4, 34, 0); using (SqlConnection cn = new SqlConnection(myConnection)) { string commandText = @" INSERT INTO DateTimeDemo (DateTimeExample,DateTimeOffsetExample) VALUES (@DateTimeExample,@DateTimeOffsetExample)"; using (SqlCommand cmd = new SqlCommand(commandText, cn)) { cmd.Parameters.AddWithValue("@DateTimeExample", DateTime.Now); cmd.Parameters.AddWithValue("@DateTimeOffsetExample", offsetValue); cn.Open(); cmd.ExecuteNonQuery(); } } } [screen shot] thanks
tbhattacharjee
The offset is the difference between UTC time and local time. To know why it is stored and for what purpose ask the creator of the database. DateTime stamps should be always stored in UTC. When displaying such values retrieved from a database, they may be converted to local time using the user/system settings. The only reasons to store the offset might be to show the stamp using the local time of the system that has written the data (not very meaningful) or knowing the time zone where the creator resides. In your example code the value it is not an offset but a fixed stamp. The real offset would be the difference between the values. So I would name that value
LocalTimeinstead ofDateTimeOffset. -
The offset is the difference between UTC time and local time. To know why it is stored and for what purpose ask the creator of the database. DateTime stamps should be always stored in UTC. When displaying such values retrieved from a database, they may be converted to local time using the user/system settings. The only reasons to store the offset might be to show the stamp using the local time of the system that has written the data (not very meaningful) or knowing the time zone where the creator resides. In your example code the value it is not an offset but a fixed stamp. The real offset would be the difference between the values. So I would name that value
LocalTimeinstead ofDateTimeOffset.thanks for reply. 1) would you post some code to grab the offset between local date and time and UTC date and time. 2) would show how to convert utc date and time to local one if we know the offset ? please share the knowledge if possible. thanks
tbhattacharjee
-
thanks for reply. 1) would you post some code to grab the offset between local date and time and UTC date and time. 2) would show how to convert utc date and time to local one if we know the offset ? please share the knowledge if possible. thanks
tbhattacharjee
It depends on the used language and the used DateTime format. If you have a format that uses the elapsed seconds or milliseconds since some epoch, just do addition / subtraction. If you have a DateTime class, use the provided class functions to add/subtract time spans and get the difference between two objects. These classes have usually also functions to convert between UTC and local time using the user or system settings for the local time zone. For JavaScript see for example Date - JavaScript | MDN[^]. For .NET see DateTime Structure (System)[^]. There is usually no need to get the offset. Use conversion functions instead. The only situation to use an offset might be when it is configured by a user setting on the server (e.g. the user has specified his time zone). Then use the language dependant addition method with a time span set to the offset. With .NET
DateTime, use for example DateTime.ToUniversalTime Method (System)[^] and DateTime.ToLocalTime Method (System)[^]. But note that the object must be in the source format (when usingToLocalTimefor example, the object must contain a UTC time). -
It depends on the used language and the used DateTime format. If you have a format that uses the elapsed seconds or milliseconds since some epoch, just do addition / subtraction. If you have a DateTime class, use the provided class functions to add/subtract time spans and get the difference between two objects. These classes have usually also functions to convert between UTC and local time using the user or system settings for the local time zone. For JavaScript see for example Date - JavaScript | MDN[^]. For .NET see DateTime Structure (System)[^]. There is usually no need to get the offset. Use conversion functions instead. The only situation to use an offset might be when it is configured by a user setting on the server (e.g. the user has specified his time zone). Then use the language dependant addition method with a time span set to the offset. With .NET
DateTime, use for example DateTime.ToUniversalTime Method (System)[^] and DateTime.ToLocalTime Method (System)[^]. But note that the object must be in the source format (when usingToLocalTimefor example, the object must contain a UTC time).i guess if i store date time in utc format and if i store also offset then i can convert utc date time to local one just adding or subtract offset hours from UTC date and time. am i right ?
tbhattacharjee
-
i guess if i store date time in utc format and if i store also offset then i can convert utc date time to local one just adding or subtract offset hours from UTC date and time. am i right ?
tbhattacharjee
Yes, you are right. But storing is only necessary if you want to track this kind of information (the local time of the system that has created the time stamp). This is often not necessary because you usually show local times using the time zone of the system displaying the time. If for example a time stamp is created by a user in India and another user somwhere in the U.S. is viewing the related data, it makes more sense to show it in the local time of the viewer or even UTC rather than using the Indian time.