Day light savings time is resulting in incorrect dates in database

Question

BACKGROUND:

I have an issue with the day light savings time change. Any records entered into the database (side note: I have no access to the script/code which enters this data to fix it) between 2021-03-28 and 2021-10-31 get entered into the database with the incorrect date. For example:

Records entered in on 2021-03-26 end up in the database as 2021-03-26 00:00:00, which is correct.

Records entered in on 2021-03-29 end up in the database as 2021-03-28 23:00:00 which is incorrect.

So when I try to search for records entered in on2021-03-26, the query works fine, but if I try to search for records entered in on 2021-03-29, it returns records from the wrong date because of the hour change.

SAMPLE DATA:

ColDate, ColName
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-28 23:00:00, SomeName -- INCORRECT DATE
2021-03-28 23:00:00, SomeName -- INCORRECT DATE
2021-03-28 23:00:00, SomeName -- INCORRECT DATE

WORKING EXAMPLE:

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-26 00:00:00'
SET @EndDate = '2021-03-26 23:59:59'

SELECT *
FROM tblName
WHERE ColDate BETWEEN @StartDate AND @EndDate

The above will return:

ColDate, ColName
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-26 00:00:00, SomeName -- CORRECT DATE
2021-03-26 00:00:00, SomeName -- CORRECT DATE

NONE WORKING EXAMPLE:

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-29 00:00:00'
SET @EndDate = '2021-03-29 23:59:59'

SELECT *
FROM tblName
WHERE ColDate BETWEEN @StartDate AND @EndDate

The above will return nothing from the sample data.

~~QUESTION:~~

How do I get around this issue? As mentioned earlier, I have no control of the data entry and the developers have no interest in fixing the issue.

Do I need to use IF statements and check if the date is between 2021-03-28 and 2021-10-31 and adjust the date by 1 hour accordingly? Or is there a better way to resolve this?

UPDATE - POSSIBLE SOLUTION:

The following query seems to work (2021-03-26):

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-26 00:00:00'
SET @EndDate = '2021-03-26 23:59:59'

SELECT
    ColDate AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time',
    ColName
FROM tblName
WHERE ColDate  BETWEEN @StartDate AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC' AND @EndDate AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC'

The following query seems to work (2021-03-29):

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-29 00:00:00'
SET @EndDate = '2021-03-29 23:59:59'

SELECT
    ColDate AT TIME ZONE 'UTC' AT TIME ZONE 'GMT Standard Time',
    ColName
FROM tblName
WHERE ColDate  BETWEEN @StartDate AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC' AND @EndDate AT TIME ZONE 'GMT Standard Time' AT TIME ZONE 'UTC'

UPDATE - QUESTION:

The above update seems to work, but am I overlooking anything?

No expert here; but you can try if this Q&A provides useful clues: https://dba.stackexchange.com/questions/161537/does-at-time-zone-automatically-take-care-of-dst-conversion — Peter B, Jun 16 '21 at 12:12
Considering that all your data *appears* to be UTC, why not work in UTC yourself? — Thom A, Jun 16 '21 at 12:24
In your non-working example, you only demonstrate that you have **no** rows for the specific date. That is NOT a DST problem. — SMor, Jun 16 '21 at 13:07
*"Thank you for the comments, it seems my only option is to use IF statements."* *Do* you? Again, if all the data is UTC, then why not, yourself, work in UTC? Or store the data as a `datetimeoffset` with the offset as `+00:00`. Then when you use a clause like `WHERE YourColumn >= '2021-03-28T00:00:00+01:00' AND YourColumn < ''2021-03-29T00:00:00+01:00'` a row with the value `2021-03-27 23:00:00+00:00` would be returned. — Thom A, Jun 16 '21 at 13:33
@Larnu, I've added an update, it seems to solve the problem. Am I overlooking anything? — oshirowanen, Jun 16 '21 at 14:03
@PeterB - Thank you, that pointed me in what seems to be the right direction. — oshirowanen, Jun 17 '21 at 09:12
@oshirowanen I think the problem is in your input program. not in your query. need to check why the incorrect data is added to the table. — Gudwlk, Jun 18 '21 at 22:41
Does the date always entered with time 00:00:00 in correct scenario? — Kazi Mohammad Ali Nur Romel, Jun 19 '21 at 19:42
Please check this https://stackoverflow.com/questions/19732896/how-to-create-daylight-savings-time-start-and-end-function-in-sql-server — Harkirat singh, Jun 20 '21 at 13:17
The difference between 'UTC' and 'GMT Standard Time' is exactly in Daylight Saving Time. UTC is never shifted with DST while DST applies to 'GMT Standard Time'. Looks like you've made an exact catch how your data source enters this column dates into the table. — Serg, Jun 21 '21 at 07:04
Aside: As a rule you shouldn't try to finesse the last _crouton_ of a day, be it a second, millisecond, ... . Instead of `... ColDate between @StartDate and @StartDateButPrettyCloseToMidnight` just use a [half-open interval](https://en.wikipedia.org/wiki/Interval_(mathematics)#Terminology): `... @StartDate <= ColDate and ColDate < DateAdd( day, 1, @StartDate )` with `<=` and `<` to avoid problems with that time just before midnight. — HABO, Jun 23 '21 at 20:14

Kazi Mohammad Ali Nur Romel · Answer 1 · 2021-06-28T19:12:36.560

Your updated answer seems great. But if the correct entered date is always without time and only incorrect ones are entered with time part then you can also just subtract 1 hour from @startdate while using it in where clause.

DECLARE @StartDate datetime
DECLARE @EndDate datetime

SET @StartDate = '2021-03-29 00:00:00'
SET @EndDate = '2021-03-29 23:59:59'

SELECT *
FROM tblName
WHERE ColDate BETWEEN dateadd(day,-1,@StartDate) AND @EndDate

But it won't work if there are rows with time part other than day light saving issue.

score 0 · Answer 2 · 2021-06-25T15:33:17.110

I think you need 2 time funcs (see bellow)

dbo.fnToDbTime for Filter times
dbo.fnToYourTime for format Result time

    DECLARE @StartDate datetime = '2021-03-28'
    DECLARE @EndDate datetime = @StartDate + 1
        
    SET @StartDate = dbo.fnToDbTime(@StartDate)
    SET @EndDate = dbo.fnToDbTime(@EndDate)      
    
    SELECT [ColDate], [ColName], dbo.fnToYourTime([ColDate]) GoodTime
    FROM tblName
    WHERE @StartDate <= ColDate AND ColDate < @EndDate
    GO

    CREATE FUNCTION dbo.fnToDbTime (@YourTime datetime)
    RETURNS datetime
    AS
    BEGIN
        DECLARE @DbTime datetime
        
        IF @YourTime < '2021-03-29 00:00:00' 
            SET @DbTime = @YourTime
        ELSE IF @YourTime < '2021-10-31 00:00:00'
            SET @DbTime = DATEADD(HH, -1, @YourTime)
        
        RETURN @DbTime
    END
    GO
    
    CREATE FUNCTION dbo.fnToYourTime (@DbTime datetime)
    RETURNS datetime
    AS
    BEGIN
        DECLARE @YourTime datetime
        
        IF @DbTime < '2021-03-28 23:00:00' 
            SET @YourTime = @DbTime
        ELSE IF @DbTime < '2021-10-30 23:00:00'
            SET @YourTime = DATEADD(HH, 1, @DbTime)
        
        RETURN @YourTime
    END
    GO

score 0 · Answer 3 · answered Jun 24 '21 at 14:26

if I am overlooking anything

You might.

You should try to understand how exactly the data entry app works. You can't change it, but you should be able to reverse engineer the code. Once you understand how the code transforms the timestamps, you may try to come up with the reversing transformation.

In your proposed solution you convert timestamp to a GMT Standard Time time zone. You should verify if your data entry app operates with this time zone. Different time zones switch between day light savings on different dates. It is not like the whole world moves their clock on the same day of the year. Some time zones don't have a day light saving at all.

Also, rules for time zones tend to change over time. Governments like to make changes. So, if your database spans across lengthy period you may discover that the rules for the time zones that are built into SQL Server now are not the same that were in place when the data was recorded. For example, Perth in Western Australia moved their clocks in 1991-1992, then again in 2006-2009, then stopped doing it. At least, DST is not observed now.

So, things to consider:

what time zone to use?
does all of your data fall into the same time zone? If the data comes from several different time zones, their rules may be different and you need to know which transformation to apply to which parts of the data.
does your data span across the period when the rules for your chosen time zone changed?

So, in general case it becomes pretty hard to fix the timestamps after the fact.

You can't change your app now, but a note for the future.

In my system I make sure to store both local and UTC time. When an entry is generated by a local computer, it usually knows its local time zone and UTC time accurately. When I have a central database with timestamps from various locations I do not attempt to convert local->UTC or UTC->local. I use appropriate field. Some reports use local times, some reports use UTC.

score 0 · Answer 4 · answered Jun 25 '21 at 06:29

Daylight (DST) time is actually an offset comparing with Local time. The majority of SQL databases has UTC internal conversion when using datetime in column type.

A very nice practice to resolve your issue is to convert your datetime string to milliseconds!!!

var myDate1 = Date.parse("2021-03-26 00:00:00")
//1616709600000
var myDate2 = Date.parse("2021-03-28 00:00:00")
//1616882400000

TIP: calculate here the time zone offset programmatic and convert it to milliseconds, now you can add it to MyDate1, MyDate2 before run your Query

var time_zone_correction = today.getTimezoneOffset()*60*1000;

When you are ready call your query with the following syntax:

DECLARE @UTC1 BIGINT 
DECLARE @UTC2 BIGINT 

SET @UTC1 = '"+myDate1+"' 
SET @UTC2 = '"+myDate2+"' 

SELECT * FROM yourTableHere WHERE [DATE] 
BETWEEN DATEADD(SECOND, @UTC1/1000, '19700101')AND DATEADD(SECOND, @UTC2/1000, '19700101') order by [DATE]"

Moreover you can check your SQL for registered timeZones by running this select * from sys.time_zone_info

Note: Milliseconds has precision over Date() object conversion from strings, also the execution time of a BIGINT compared to string datetime conversion is much faster.

In terms of code re-usability, if you add your offset (milliseconds calculation) for time zones before executing your query and not inside, you end up with a dynamic and more "template" approach of writing a query.

Give it a try and research the web, there are tons of tutorials for dealing with UTC, DST and timezone manipulation!

I hope my approach will help you!

Day light savings time is resulting in incorrect dates in database

4 Answers4