UTC and daylight saving time scenario

Question

I use UTC to store data and time values ​​in a DB. Values ​​are converted to local time on the client or in the client’s time zone. I went on to these scenarios from an article in the MSDN article , where displaying time from UTC seems to create problems with summer savings.

Someone living on the east coast of the United States distinguishes value such as, "October 26, 2003 01:10:00."

1) On this special morning, due to daylight, at 2 a.m., the local clock will reset to 1:00 a.m., creating a 25-hour work day. Since all the hours are between 1:00 AM and 2:00 in the morning, twice especially in the morning - at least in most of the USA and Canada, the computer really has no way to find out what it meant 1:10 in the morning - the one that happens before the switch, or one that happens 10 minutes after daylight saving time.

2) Similarly, the problem occurs in the spring, when, in particular in the morning, there is no time 2:10 AM. The reason is that at 2 o’clock especially in the morning, the time on the local clock suddenly changes at 3:00. The whole 2:00 o'clock never happens this 23-hour day.

How did you deal with situation No. 1, when you could have 4 transactions, two before switching and two after switching to daylight saving time? How to display the time for the user for transactions, since the last two transactions can be displayed ahead of time than the first two transactions due to a shift.? This can sometimes be counterintuitive, for example, in a mail chain.

ADDED:

To add additional contextual information, RIA applications, such as running Silverlight / Flash on the client (or any client application that talks to the server through Webservice), allow the user to choose a delivery time or schedule with the local PC time.

, . , , , .

# :

//2:30 am CT to UTC --> 8:30 am  
DateTime dt = new DateTime(2009, 03, 08, 2, 30, 00, DateTimeKind.Local);  

//8:30 am UTC to CT --> 3:30 am.. which is as expected  
DateTime dt1 = new DateTime(2009, 03, 08, 8, 30, 00, DateTimeKind.Utc);  

//check for daylight saving time returns false.. ??  
TimeZoneInfo.Local.IsDaylightSavingTime(dt);  

//check for daylight saving time returns true  
TimeZoneInfo.Local.IsInvalidTime(dt);  

Answer 1

, DST. , , UTC. , UTC , , , .

, : 12:30, 1:20, 1:10, 3:30, UTC ( ), , .

SO , UTC, UTC (, "17 ..." ).

---

/, , : . , , - . , , , 1 , , ( , -time, DST).

Answer 2

.

( roundtrip)

2009-08-11T13:22:13.8493713-04:00

-5, -4.

26 , 01:10,

2009-10-26T1:10:00.0000000-04:00

02:00, ,

2009-10-26T1:10:00.0000000-05:00

.NET, 2.0sp1, DateTimeOffset. SqlServer 2008 DateTimeOffset, . SqlServer 2008, , roundtrip:

DateTime.Now.ToString("o")

Answer 3

, , - .? , , .

UTC .

, , :

01:10:00
01:50:00
01:05:00
01:20:00

, UTC.

Answer 4

-, , , UTC. "" . , , , , UTC, - .:)

Answer 5

:

, . 1, , . ( , ?), , . ( ), .

2, , UTC. , . , , . , .. -, (EST EDT, GMT vs BST ..). . , , , , , , . , UTC, .

Answer 6

SQL Server has a new type of "datetimeoffset" that does a great job of this since you can have the same time with different offsets. For my SQL Server 2005 database, I use a string literal of this type, nvarchar (25) of the form "YYYY-MM-DD hh: mm: ss-hh: mm".

To do this, I created routines to convert these strings to the correct times.

Answer 7

I solved this for NZ time, as shown below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Vivi Woolford
-- Create date: 27-09-2016
-- Description: This procedure only Works in New Zealand
-- =============================================
CREATE FUNCTION [dbo].[udf_GetLocalTimeFromUTC] 
(   
    @UTCTime DATETIME
)
RETURNS DATETIME
AS
BEGIN
    --Daylight Saving commences on the last Sunday in September, when 2.00am becomes 3.00am. 
    --It ends on the first Sunday in April, when 3.00am becomes 2.00am.
    DECLARE @LocalTime DATETIME 
    DECLARE @OffSet INT = 12

    SELECT @LocalTime = DATEADD(HOUR, @OffSet, @UTCTime)

    IF @LocalTime BETWEEN 
    /*FINISH DAY LIGHT SAVINGS*/
    DATEADD(HOUR, 2, DATEADD(dd, (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(@LocalTime)-1900) * 12 + 3,0))%7)),DATEADD(mm,(YEAR(@LocalTime)-1900) * 12 + 3,0)))
    AND     
    /*START DAY LIGHT SAVINGS*/
    DATEADD(HOUR, 2, DATEADD(dd, -1*(DATEPART(dw, DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @LocalTime)+1, 0)))-1),DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @LocalTime)+1, 0))))     
    BEGIN
        SELECT @LocalTime = @LocalTime 
    END 
    ELSE
    BEGIN
        SELECT @LocalTime = DATEADD(HOUR, 1, @LocalTime)
    END

    RETURN @LocalTime

END

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vivi Woolford
-- Create date: 27-09-2016
-- Description: This procedure only Works in New Zealand
-- =============================================
ALTER FUNCTION [dbo].[udf_GetUTCFromLocalTime]
(   
    @LocalTime DATETIME
)
RETURNS DATETIME
AS
BEGIN
    --Daylight Saving commences on the last Sunday in September, when 2.00am becomes 3.00am. 
    --It ends on the first Sunday in April, when 3.00am becomes 2.00am.
    DECLARE @UTCTime DATETIME   
    DECLARE @OffSet INT = 12

    IF @LocalTime BETWEEN 
    /*FINISH DAY LIGHT SAVINGS*/
    DATEADD(HOUR, 2, DATEADD(dd, (6-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(@LocalTime)-1900) * 12 + 3,0))%7)),DATEADD(mm,(YEAR(@LocalTime)-1900) * 12 + 3,0)))
    AND     
    /*START DAY LIGHT SAVINGS*/
    DATEADD(HOUR, 2, DATEADD(dd, -1*(DATEPART(dw, DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @LocalTime)+1, 0)))-1),DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @LocalTime)+1, 0))))
    BEGIN
        SELECT @UTCTime = DATEADD(HOUR, -@OffSet, @LocalTime)
    END 
    ELSE 
    BEGIN
        SELECT @UTCTime = DATEADD(HOUR, -1-@OffSet, @LocalTime)
    END

    RETURN @UTCTime

END
go