1
\$\begingroup\$

The following constellation:

Our applications passes date&time around (in the most horrible way possible).

We want to simplify this. Instead of a culture-specific string like '31.12.2019', we're now passing an ecma-timestamp (the number of milliseconds between the point in time in UTC and 1970-01-01 00:00:00 UTC that is).

Now an additional complication:

Our pitiful application historically has saved all datetime values in the database as local time WITH DAYLIGHT-SAVING (central European summer or winter time, depending on the date) instead of UTC.

Now, central European summer time (CEST) is UTC+2, while central European winter time (CET) is UTC+1.

For the adjustment between summer and winter time, the following rules are applied:

  • The change from winter time to summer time is on the last Sunday of March

    • On the last Sunday morning of March, the clocks will be put forward from 02:00 to 03:00. (one 'loses' an hour)
  • The change from summer time to winter time is on the last Sunday of October:

    • On the last Sunday morning of October, the clocks will be put backward from 03:00 to 02:00 (one wins an hour)

As you might realize from looking at the definition, the change from summer to winter time presents a discontinuity range, in which a given local-time value can be both summer or winter time... (but not the change from winter to summer time)

Now, I have written the below functions to convert local/UTC-time into an ECMA-timestamp, and you can specify if the input datetime is UTC or localtime.

I haven't had the time to test it all too extensively, but I'd like to collect a second opinion on how to handle the times between 02 and 03 o'clock at the last Sunday of October...

  • Would you handle the conversion the same? (apart from the fact that ideally, the conversion would be avoided / data changed to UTC)
  • Do you spot any errors?
  • Thoughts on what best to do between 02 and 03

PRINT 'Begin Executing "01_fn_dtLastSundayInMonth.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_dtLastSundayInMonth]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fn_dtLastSundayInMonth]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 



/*
-- This is for testing
SET DATEFIRST 3; -- Monday

WITH CTE AS (

    SELECT 1 AS i, CAST('20190101' AS datetime) AS mydate  
    UNION ALL 

    SELECT i+1 AS i, DATEADD(month, 1, CTE.mydate) AS mydate 
    FROM CTE WHERE i < 100 
)

SELECT -666 AS i, dbo.fn_dtLastSundayInMonth('17530101') AS lastSundayInMonth, dbo.fn_dtLastSundayInMonth('17530101') AS Control 

UNION ALL 

SELECT -666 AS i, dbo.fn_dtLastSundayInMonth('99991231') AS lastSundayInMonth, dbo.fn_dtLastSundayInMonth('99991231') AS Control 

UNION ALL 

SELECT 
     mydate 
    ,dbo.fn_dtLastSundayInMonth(mydate) AS lastSundayInMonth 
    ,dbo.fn_dtLastSundayInMonth(mydate) AS lastSundayInMonth 
    ,DATEADD(day,DATEDIFF(day,'19000107', DATEADD(MONTH, DATEDIFF(MONTH, 0, mydate, 30))/7*7,'19000107') AS Control 
FROM CTE 

*/


-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Return Datum von letztem Sonntag im Monat 
--                mit gleichem Jahr und Monat wie @in_DateTime
-- =====================================================================
ALTER FUNCTION [dbo].[fn_dtLastSundayInMonth](@in_DateTime datetime )
RETURNS DateTime
AS
BEGIN
    -- Abrunden des Eingabedatums auf 00:00:00 Uhr
    DECLARE @dtReturnValue AS DateTime  
    -- 26.12.9999   SO
    IF @in_DateTime >= CAST('99991201' AS datetime) 
        RETURN CAST('99991226' AS datetime); 

    -- @dtReturnValue is now last day of month 
    SET @dtReturnValue = DATEADD 
        (
             DAY 
            ,-1
            ,DATEADD
            (
                 MONTH
                ,1
                ,CAST(CAST(YEAR(@in_DateTime) AS varchar(4)) + RIGHT('00' + CAST(MONTH(@in_DateTime) AS varchar(2)), 2) + '01' AS datetime) 
            )
        )
    ;

    -- SET DATEFIRST 1 -- Monday - Super easy ! 
    -- SET DATEFIRST != 1 - PHUK THIS ! 
    SET @dtReturnValue = DATEADD
                        (
                            day
                            ,
                             -
                             (

                                (
                                    -- DATEPART(WEEKDAY, @lastDayofMonth) -- with SET DATEFIRST 1 
                                    DATEPART(WEEKDAY, @dtReturnValue) + @@DATEFIRST - 2 % 7 + 1 
                                )
                                %7
                            )
                            , @dtReturnValue
    );

    RETURN @dtReturnValue; 
END


GO



GO 


PRINT 'Done Executing "01_fn_dtLastSundayInMonth.sql"' 


GO 





PRINT 'Begin Executing "02_fn_dtIsCEST.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_dtIsCEST]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fn_dtIsCEST]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 




-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Ist @in_DateTime Mitteleuropäische Sommerzeit ? 
-- =====================================================================
-- SELECT dbo.fn_dtIsCEST('2019-03-31T01:00:00'), dbo.fn_dtIsCEST('2019-03-31T04:00:00') 
ALTER FUNCTION [dbo].[fn_dtIsCEST](@in_DateTime datetime )
RETURNS bit 
AS
BEGIN
    DECLARE @dtReturnValue AS bit   

    -- https://www.linker.ch/eigenlink/sommerzeit_winterzeit.htm

    -- the change from winter time to summer time is on the last sunday of March
    -- the clocks will be put forward from 02:00 to 03:00. (one 'loses' an hour)

    -- the change from summer time to winter time is on the last sunday of October:
    -- the clocks will be put backward from 03:00 to 02:00 (one wins an hour).

    DECLARE @beginSummerTime datetime 
    SET @beginSummerTime = dbo.fn_dtLastSundayInMonth(DATEADD(MONTH, 2, DATEADD(YEAR, YEAR(@in_DateTime)-1900, 0)) )  
    SET @beginSummerTime = DATEADD(HOUR, 2, @beginSummerTime) 

    DECLARE @beginWinterTime datetime 
    SET @beginWinterTime = dbo.fn_dtLastSundayInMonth(DATEADD(MONTH, 9, DATEADD(YEAR, YEAR(@in_DateTime)-1900, 0)) )   
    SET @beginWinterTime = DATEADD(HOUR, 2, @beginWinterTime) 

    SET @dtReturnValue = 0; 
    IF @in_DateTime >= @beginSummerTime AND @in_DateTime < @beginWinterTime 
    BEGIN 
        SET @dtReturnValue = 1;
    END 

    RETURN @dtReturnValue;
END


GO



GO 


PRINT 'Done Executing "02_fn_dtIsCEST.sql"' 


GO 





PRINT 'Begin Executing "03_fn_dtToEcmaTimeStamp.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_dtToEcmaTimeStamp]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fn_dtToEcmaTimeStamp]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 




-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Ist @in_DateTime Mitteleuropäische Sommerzeit ? 
-- =====================================================================
-- SELECT dbo.fn_dtToEcmaTimeStamp('2019-03-31T01:00:00', 1), dbo.fn_dtToEcmaTimeStamp('2019-03-31T04:00:00', 1) 
ALTER FUNCTION [dbo].[fn_dtToEcmaTimeStamp](@in_DateTime datetime, @in_convert_to_utc bit)
RETURNS bigint 
AS
BEGIN
    DECLARE @dtReturnValue AS bigint    

    IF @in_convert_to_utc = 1 
    BEGIN
        SET @in_DateTime = 
        CASE WHEN dbo.fn_dtIsCEST(@in_DateTime) = 1 
            THEN DATEADD(HOUR, -2, @in_DateTime) 
            ELSE DATEADD(HOUR, -1, @in_DateTime) 
        END;
    END 

    SET @dtReturnValue = 
        CAST
        (
            DATEDIFF
            (
                HOUR
                ,CAST('19700101' AS datetime)
                ,@in_DateTime 
            )
            AS bigint
        ) *60*60*1000
        + 
        DATEDIFF
        (
             MILLISECOND 
            ,CAST(FLOOR(CAST(@in_DateTime AS float)) AS datetime) 
            ,@in_DateTime 
        ) % (60*60*1000)
    ;

    RETURN @dtReturnValue;
END


GO



GO 


PRINT 'Done Executing "03_fn_dtToEcmaTimeStamp.sql"' 


GO 





PRINT 'Begin Executing "04_fn_dtFromEcmaTimeStamp.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_dtFromEcmaTimeStamp]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fn_dtFromEcmaTimeStamp]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 



-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Ist @in_DateTime Mitteleuropäische Sommerzeit ? 
-- =====================================================================
-- SELECT dbo.fn_dtFromEcmaTimeStamp('1551437088122', 1), dbo.fn_dtFromEcmaTimeStamp('1554069600000', 1) 
ALTER FUNCTION [dbo].[fn_dtFromEcmaTimeStamp](@in_timestamp bigint, @in_convert_to_localtime bit)
RETURNS datetime 
AS
BEGIN
    DECLARE @dtReturnValue AS datetime      
    DECLARE @hours int 
    SET @hours = @in_timestamp /(1000*60*60);

    DECLARE @milliseconds int 
    SET @milliseconds = @in_timestamp - (@in_timestamp /(1000*60*60))*(1000*60*60);


    SET @dtReturnValue = DATEADD 
                        (
                            MILLISECOND, @milliseconds, 
                            DATEADD(hour, @hours, CAST('19700101' AS datetime)) 
                        ) 


    IF @in_convert_to_localtime = 1 
    BEGIN
        SET @dtReturnValue = DATEADD(HOUR, 1, @dtReturnValue) 
        SET @dtReturnValue = 
                                CASE WHEN dbo.fn_dtIsCEST(@dtReturnValue) = 1 
                                    THEN DATEADD(HOUR, 1, @dtReturnValue) 
                                    ELSE @dtReturnValue 
                                END;
    END 

    RETURN @dtReturnValue;
END


GO



GO 


PRINT 'Done Executing "04_fn_dtFromEcmaTimeStamp.sql"' 


GO 
\$\endgroup\$
  • \$\begingroup\$ "and you can specify if the input datetime is UTC or localtime" Why not store all dates/times in the exact same timezone and let the client handle the conversion to whatever time the viewer wants? \$\endgroup\$ – Mast Mar 20 at 11:55
  • \$\begingroup\$ Is this all supposed to be one query or multiple queries that accidentally got stacked together in one codeblock? \$\endgroup\$ – Mast Mar 20 at 11:55
  • 1
    \$\begingroup\$ @Mast: Because several very large application(s) that handles time that wrong internally just works with local time. You can't just change the data on the database, you first need to change the application as well. By specifying it as parameter, you can use UTC where changed, and localtime where the change still needs to be done. \$\endgroup\$ – Quandary Mar 20 at 12:00
  • 1
    \$\begingroup\$ @Mast: These are 4 functions that were merged into one script. Function 1 to get the last sunday of month x in year y, function 2 to determine if a local datetime is summer or winter-time, function 3 to to convert datetime to ecma-timestamp, function 4 to convert from ecma-timestamp to datetime. \$\endgroup\$ – Quandary Mar 20 at 12:03
  • 2
    \$\begingroup\$ I don't envy you for your problem. A thought I had that may or may not apply: If you have an autoincrement primary key or another way to determine ordering between records aside from the date, you can use that to find out when the DST rollover happened for one given period. That's unfortunately rather nontrivial and depends on a column existing that is monotonous as a function of time... \$\endgroup\$ – Vogel612 Mar 20 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.