CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetimedst
Matt Weick
Matt Weick

Reputation: 332

Incorporate DST into SQL script

My original question was too compact and all over the place so I tried to clean it up here. Trying to figure out how to properly implement DST into my SQL script for USA.

a.ActualEnd = DateTime value & c.TimeZoneBias = minutes to offset based on users local time

UPDATE: Entire Script Below

INSERT INTO [AS400].S062f7ar.APLUS83MDS.PEPAPPTS01  
(PPCONO, PPREP1, PPDATE, PPCOUNT)
select '1' as PPCONO, 
       b.new_SalesrepId as PPREP1, 
       MAX(CONVERT(varchar(8), 
       (a.ActualEnd - c.TimeZoneBias / cast(24 * 60 as float)), 112)) as PPDATE,       
       count(b.new_SalesrepId) as PPCOUNT 
from ActivityPointerBase as a
  join SystemUserExtensionBase as b 
   on b.SystemUserId = a.OwnerId
  join UserSettingsBase as c 
   on c.SystemUserId = b.SystemUserId
where b.new_SalesrepId <> '99999999' 
 and a.ActivityTypeCode = '4201' 
 and b.new_SalesrepId is not NULL 
 and a.StateCode = '1' 

 and CONVERT(varchar(8), 
             a.ActualEnd - c.TimeZoneBias / cast(24 * 60 as float), 
             112) >= dateadd(day,datediff(day,
                                            1,
                                          CONVERT(varchar(8), 
                                                  GetDate(), 
                                                  112)
                                         ),
                             0) 
 and CONVERT(varchar(8), 
             a.ActualEnd - c.TimeZoneBias / cast(24 * 60 as float), 
             112) < dateadd(day,datediff(day,
                                           0,
                                         CONVERT(varchar(8), 
                                                 GetDate(), 
                                                 112)
                                         ),
                             0)
 group by b.new_SalesrepId, 
   CONVERT(varchar(8), 
   (a.ActualEnd - c.TimeZoneBias / cast(24 * 60 as float)), 112)
 order by b.new_SalesrepId ASC;

Looking to properly incorporate DST for USA into this where statement. I don't want to have to manually change the script every time DST rolls around.

Upvotes: 0

Views: 234

Answers (1)

Sybeus
Sybeus

Reputation: 1189

Assumptions

Since these points are not explicitly defined in the question, I am stating my assumptions of them here.

  1. The current server stores all dates and times in the database are UTC.
  2. The query is to return all dates and times in user local time.
  3. The query is to return all records within a date range, but according to each user's local time.
  4. TimeZoneBias is an Americanized bias in minutes where a value of 420 is UTC-(420/60), or UTC-7 (MST), and value of -120 is UTC-(-120/60), or UTC+2 (EET).

Solutions

I see three possible solutions. I believe all of them could be feasible, assuming a bit of data migration is performed first on a couple of them.

Solution 1 : DATETIMEOFFSET

Implement ActualEnd column as DATETIMEOFFSET type in table ActivityPointerBase. The application will be required to provide timestamps with the user's current local offset. Then simply use CONVERT(DATE, a.ActualEnd) to get the user's local date later on. Even allows you to compare timestamps between users because the database can implicitly do the offset calculations when comparing two DATETIMEOFFSET values. And there are functions to convert DATETIMEOFFSET to a UTC DATETIME for comparing against other tables.

SELECT '1' as PPCONO,
       b.new_SalesrepId AS PPREP1,
       CONVERT(VARCHAR(8), a.ActualEnd, 112) AS PPDATE,
       COUNT(b.new_SalesrepId) AS PPCOUNT
FROM ActivityPointerBase AS a
JOIN SystemUserExtensionBase AS b ON b.SystemUserId = a.OwnerId
WHERE b.new_SalesrepId <> '99999999' 
AND a.ActivityTypeCode = '4201' 
AND b.new_SalesrepId IS NOT NULL 
AND a.StateCode = '1' 
AND CONVERT(DATE, a.ActualEnd) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))
GROUP BY b.new_SalesrepId,
         CONVERT(VARCHAR(8), a.ActualEnd, 112)
ORDER BY b.new_SalesrepId ASC;

Solution 2: Store data you need

Store both the user local time and UTC time in the database. Since you obviously require user local time later on, make storing it a requirement of your system. Then you have both an ActualEnd and UserActualEnd column in the ActivityPointerBase table.

SELECT '1' as PPCONO,
       b.new_SalesrepId AS PPREP1,
       CONVERT(VARCHAR(8), a.UserActualEnd, 112) AS PPDATE,
       COUNT(b.new_SalesrepId) AS PPCOUNT
FROM ActivityPointerBase AS a
JOIN SystemUserExtensionBase AS b ON b.SystemUserId = a.OwnerId
WHERE b.new_SalesrepId <> '99999999' 
AND a.ActivityTypeCode = '4201' 
AND b.new_SalesrepId IS NOT NULL 
AND a.StateCode = '1' 
AND CONVERT(DATE, a.UserActualEnd) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))
GROUP BY b.new_SalesrepId,
         CONVERT(VARCHAR(8), a.UserActualEnd, 112)
ORDER BY b.new_SalesrepId ASC;

Solution 3: Reconstruct user local time

Build a system where you can reconstruct user local time from UTC timestamps. I'll outline a simple one below, but it is by no means tested nor the only way to reconstruct them.

First, you're going to need a ObserveDst column in the UserSettingsBase table, because some states follow DST and some do not, therefore a TimeZoneBias is not enough data to reconstruct user local time.

  ObserveDst INT NOT NULL

Which will contain a value of 1 if they observe DST and 0 otherwise.

Second, you're going to need a table of the DST start and end timestamps, since they have changed the definitions over time, and could possibly do so again in the future. I suggest a table that is broken up into intervals. This way you can perform simple joins with other tables instead of defining and calling a function that operates per record.

CREATE TABLE Dst
(
  BeginDT DATETIME NOT NULL,
  EndDT DATETIME NOT NULL,
  DstBias INT NOT NULL,
  PRIMARY KEY(BeginDT)
);

And put an index of (BeginDT, EndDT, DstBias) on the table. It's okay it includes all columns in this case because it's not going to be a very big table. For 2014, you'd have the following records:

('2014-01-01 00:00:00', '2014-03-09 02:00:00', 0)
('2014-03-09 02:00:00', '2014-11-02 03:00:00', 60)
('2014-11-02 03:00:00', '2015-01-01 00:00:00', 0)

The November hour may look a bit odd, but the purpose of this table is to move from local non-DST time to local DST time. And 2014-11-02 03:00:00 EST is 2014-11-02 02:00:00 EDT. Also, be aware that these are closed-open intervals, which means including the first timestamp and up to but not including the last timestamp. If you have historic dates going back before DST, you can compress them all into a single interval. You could even join the beginning and end intervals of each year, giving you only N+3 records for N years.

The United States Department of Transportation defines the federally mandated dates, for states that choose to observe Daylight Savings Time (http://www.dot.gov/regulations/daylight-saving-time). I suggest you look to them, or another reasonably official site for the correct dates for the years you need.

Then you just join the Dst table and perform basic offset calculations.

SELECT '1' as PPCONO,
       b.new_SalesrepId AS PPREP1,
       CONVERT(VARCHAR(8), CONVERT(DATE, DATEADD(MINUTE, d.DstBias*c.ObserveDst-c.TimeZoneBias, a.ActualEnd)), 112) AS PPDATE,
       COUNT(b.new_SalesrepId) AS PPCOUNT
FROM ActivityPointerBase AS a
JOIN SystemUserExtensionBase AS b ON b.SystemUserId = a.OwnerId
JOIN UserSettingsBase AS c ON c.SystemUserId = b.SystemUserId
JOIN Dst AS d ON DATEADD(MINUTE, -c.TimeZoneBias, a.ActualEnd) >= d.BeginDT AND DATEADD(MINUTE, -c.TimeZoneBias, a.ActualEnd) < d.EndDT
WHERE b.new_SalesrepId <> '99999999' 
AND a.ActivityTypeCode = '4201' 
AND b.new_SalesrepId IS NOT NULL 
AND a.StateCode = '1' 
AND CONVERT(DATE, DATEADD(MINUTE, d.DstBias*c.ObserveDst-c.TimeZoneBias, a.ActualEnd)) = CONVERT(DATE, DATEADD(DAY, -1, GETDATE()))
GROUP BY b.new_SalesrepId,
         CONVERT(VARCHAR(8), CONVERT(DATE, DATEADD(MINUTE, d.DstBias*c.ObserveDst-c.TimeZoneBias, a.ActualEnd)), 112)
ORDER BY b.new_SalesrepId ASC;

Upvotes: 1

Related Questions