DATEADD and time zone support - any known issues?
-
G'day all,
This is an open discussion question rather than a current problem.
I am running a data collection system that operates globally. The server has all data normalized to GMT (Monrovia) and aggregated to an hourly granularity. I now need to pull the data back out in the time zone of the end user. For example, displaying data for the east coast of the USA requires an adjustment from GMT to either GMT-5 or GMT-4 depending on the day of the year. Rather than reinvent all of the date manipulation routines already built into various layers of the stack in cluding SQL Server, I would like to simply use the DATEADD function. I am building a test harness to explore the problem with real data, but would like to avoid as many mistakes as possible. Now the questions.
How have you dealt with this issue in your systems?
What are the lessons learned?
What are the best practices? Worst practices?
Thanks in advance, and have a great day!
Wayne
-
I fortunately have dealt only with eastern time zone so I have an SP to do the conversion (SQL 7 however I retired that code) or a com object. Personally I find preference in the COM Object as it is a bit more structured and easier to work with for me.
This thing is a pain to do in many places so find code that works and reuse it.
I haven't seen a best worst scenario except that if you need to move between timezones it is best to have in GMT to ensure you have a fixed time flow unlike a changing one cause in fall you repeat 1 to 2 am over and makes hard to distinguish if asked. But it is in my experience better to do at the client end when possible so you can use their localized settings to determine the correct value.
-
In a way I think of this as a constraint, like any other in a db.
You have a basevalue, but for it to be correct in regard to the viewer, it must be adjusted according to a parameter that only the viewer knows...
To ensure (from the db level) that you always display the 'correct' values, I do believe that it's best to place the logic on the server. This requires only a single instance of that code, reducing the possibility of clients running on different code, and at the same time, requiring the viewer to pass the parameter in order to view anything at all.
So.. I like the idea of DATEADD. It's simple. Just have the client send it's time offset as a parameter, and you're done... (hopefully
)/Kenneth
-
Just for fun, you can do this from with T-SQL
DECLARE @delta INT
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE'
, 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation'
, 'ActiveTimeBias'
, @delta OUT
SELECT GETDATE() AS Aktuelle_Zeit
, DATEADD( Minute
, @delta,GETDATE()) AS Greenwich_Mean_Time
, @delta as Delta
The only caveat is that xp_regread is undocumented
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
...another caveat may be that it will show the server's time, not the clients - so the server won't now which offset the client has anyway..
..but xp_regread is indeed useful
/Kenneth
-
I knew, I was missing something
.Btw, Kenneth, is the SSWUG SQL2k dead?
Not much mails there lately.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
All good comments. Kenneth has picked out the key issue - retrieving the data in the time zone of the user. The application stack is pulling the users time zone from the browser info and passing that down to the data layer. From a constraints perspective, we know the base data is in GMT, and we know the desired display time zone. The challenge is now, for example, summing a day of data relative to the users time zone. Rather than 00:01 to 24:00 in GMT, the actual "day" may be 05:01 to 05:00.
I am curious how well, if at all, DATEADD comprehends time zones and daylight savings time in particular. I'll post the results from the test harness later today.
/Wayne
-
Here is the code from the VB ActiveX DLL I use currently. Like I said thou I only use Eastern so I defaulted for my needs.
Public Function CvrtToGMT(DateIn As Date, Optional Zone As String = "Eastern", Optional ST As Boolean = False) As Date 'Convert Dates to GMT time from zone input
Dim DateTemp As Date
Dim BaseVal As Date
Dim DayVal As Integer
Dim ZoneCtrl As Integer
Dim ZoneCtrl2 As Integer
GetDiff Zone, ZoneCtrl, ZoneCtrl2
If DatePart("m", DateIn) < 10 Then
BaseVal = CDate("4/1/" & DatePart("yyyy", DateIn) & " 2:00 AM")
Else
BaseVal = CDate("10/31/" & DatePart("yyyy", DateIn) & " 2:00 AM")
End If
DayVal = DatePart("w", BaseVal)
If DayVal <> 1 Then
If DatePart("m", BaseVal) = 4 Then
BaseVal = DateAdd("d", 8 - DayVal, BaseVal)
Else
BaseVal = DateAdd("d", -DayVal + 1, BaseVal)
End If
End If
If DatePart("m", BaseVal) = 10 Then
If DateIn < BaseVal Then
If DatePart("h", DateIn) = 1 And ST = True Then
DateTemp = DateAdd("h", ZoneCtrl2, DateIn)
Else
DateTemp = DateAdd("h", ZoneCtrl, DateIn)
End If
Else
DateTemp = DateAdd("h", ZoneCtrl2, DateIn)
End If
Else
If DateIn <= BaseVal Then
DateTemp = DateAdd("h", ZoneCtrl2, DateIn)
Else
DateTemp = DateAdd("h", ZoneCtrl, DateIn)
End If
End If
CvrtToGMT = DateTemp
End Function
Public Function CvrtFromGMT(DateIn As Date, Optional Zone As String = "Eastern") As Date 'Convert Dates from GMT time to zone input
Dim DateTemp As Date
Dim BaseVal As Date
Dim DayVal As Integer
Dim ZoneCtrl As Integer
Dim ZoneCtrl2 As Integer
GetDiff Zone, ZoneCtrl, ZoneCtrl2
If DatePart("m", DateIn) < 10 Then
BaseVal = CDate("4/1/" & DatePart("yyyy", DateIn) & " " & (2 + ZoneCtrl2) & ":00 AM")
Else
BaseVal = CDate("10/31/" & DatePart("yyyy", DateIn) & " " & (2 + ZoneCtrl) & ":00 AM")
End If
DayVal = DatePart("w", BaseVal)
If DayVal <> 1 Then
If DatePart("m", BaseVal) = 4 Then
BaseVal = DateAdd("d", 8 - DayVal, BaseVal)
Else
BaseVal = DateAdd("d", -DayVal + 1, BaseVal)
End If
End If
If DatePart("m", BaseVal) = 10 Then
If DateIn < BaseVal Then
DateTemp = DateAdd("h", -(ZoneCtrl), DateIn)
Else
DateTemp = DateAdd("h", -(ZoneCtrl2), DateIn)
End If
Else
If DateIn <= BaseVal Then
DateTemp = DateAdd("h", -(ZoneCtrl2), DateIn)
Else
DateTemp = DateAdd("h", -(ZoneCtrl), DateIn)
End If
End If
CvrtFromGMT = DateTemp
End Function
Public Function GetDiff(Zone As String, DST As Integer, ST As Integer)
Select Case Zone
Case "East", "Eastern"
DST = 4
ST = 5
Case "Cent", "Central"
DST = 5
ST = 6
Case "Mnt", "Mountain"
DST = 6
ST = 7
Case "AZ", "Arizona"
DST = 6
ST = 6
Case "Pac", "Pacific"
DST = 7
ST = 8
Case "AK", "Alaska"
DST = 9
ST = 9
Case "HI", "Hawaii"
DST = 10
ST = 10
End Select
End Function
-
Perhaps most users there has run out of tricky questions..?
No, it's not dead to my knowledge.. Posts keep trickling in still, but maybe not as many as before. It goes up and down.
/Kenneth
-
AFAIK, DATEADD has no knowledge of timezones or daylightsavings.
But if the users offset from GMT is known and you have your date in myGMTtime column, then something like this would 'convert' the GMT times according to the users perspective.
declare @clientOffset int
set @clientOffset = -5
SELECT myGMTtime,
DATEADD(hh, @clientOffset, myGMTtime) as 'clientTime'
FROM .....
/Kenneth
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply