UTC Dates
-
I am looking for a way to get a utc date for any date and to obtain the local date for any utc date. The system has dates that have been transformed into GMT by the business tier but we are trying to report on the data.
Any help would be appreciated
-
In SQL Server 2000, this will get you the offset from UTC for the server:
DATEDIFF(hh,GETUTCDATE(),GETDATE())You can then convert back and forth using this offset with DATEADD.
--Jonathan
--Jonathan -
Johnathan
Thanks for the reply but I have already ruled that solution out as the date may be from a period when the offset was different from what it is at the moment.
What I am looking for is a way of getting the system to transform any date into the local time.
-
quote:
JohnathanThanks for the reply but I have already ruled that solution out as the date may be from a period when the offset was different from what it is at the moment.
What I am looking for is a way of getting the system to transform any date into the local time.
I think you get to write UDFs using the relevant DST algorithms, then. Best practice would be to have columns for both times (or one time and the offset; a tinyint only uses one byte) in the database, but this advice is probably not in time.
--Jonathan
--Jonathan -
Interesting scenario.
What if the producer of the data is located in New York, the SQL Server in London and Consumer / Report Reader is located in Sydney.
The dates in the database is in GMT, should'nt the translation be performed on the fly during report creation because only then on can be sure on which time zone the user would like to see the data. Also when the Sydney based report reader ask for a report the parameters needs to be translated into GMT and the database queried with the resulting dates converted back to the users local time
Sounds like the offset should be a parameter for the sp because if the company / system goes more international more offsets may become relavant.DST = Daylight Savings Time
-
quote:
Interesting scenario.What if the producer of the data is located in New York, the SQL Server in London and Consumer / Report Reader is located in Sydney.
The dates in the database is in GMT, should'nt the translation be performed on the fly during report creation because only then on can be sure on which time zone the user would like to see the data. Also when the Sydney based report reader ask for a report the parameters needs to be translated into GMT and the database queried with the resulting dates converted back to the users local time
Sounds like the offset should be a parameter for the sp because if the company / system goes more international more offsets may become relavant.Yes, I think you've got a good point. Keep just the UTC values and have the reporting application layer request the client's time zone and filter the values (performance hit there) based on a function that understands time zones (as does the Windows API that automatically adjusts system time). As Richard pointed out, it's more complicated than just a constant offset, due to the seasonal shifts in many time zones, and I wouldn't want to write a universal function (and table?) in SQL Server.
On second thought, just leave UTC in the reports and make the users mentally convert. quote:
DST = Daylight Savings TimeYes, sorry. It just happened here...
--Jonathan
--Jonathan -
Specially this one
http://www.mvps.org/vbnet/index.html?code/locale/gettimezonebias.htm
-
quote:
Specially this onehttp://www.mvps.org/vbnet/index.html?code/locale/gettimezonebias.htm
I'm not going to spend any more time on this, but you'd also need to get the transition dates (and times?) for when any "daylight" factor applies to a zone. That's not in these kernel calls, which just demo the current biases...
--Jonathan
--Jonathan -
The problem is easy when you want to convert the CURRENT time from UTC to local time or vice versa.
The difficulty is, as I've already run into, to say what WAS a local time several month agy from a date stored as UTC date.
This is our case.
I'm unable to tell exactly in my helpdesk reports when exactly the customers has started to call, because all the times are store as UTC times. and I don't know when the DST change occured.
Bye
Gabor
Bye
Gabor -
These are the bits I use in either sp or udf, I did have a calculate bit I built but cannot find right now. I had been working on a way to get it based on user input difference but did not finish.
Hpe this helps.
--This Code Converts GMT To local time defined by offsets.
Declare @DateUse datetime
Declare @TimeChg datetime
Declare @DayVal NUMERIC
Declare @GMT datetime
Declare @DST smallint
Declare @ST smallint
SET @DST = -4-- GMT 4 hours ahead of Eastern
SET @ST = -5 -- GMT 5 hours ahead of Eastern
SET @DateUse = '10/28/2003' --Date time we are working with.
If Month(@DateUse) >= 10 --Check To See if Date is Oct, Nov, or Dec
BEGIN
Set @TimeChg = '10/31/' + Cast(Year(@DateUse) as varchar) --Use Oct 31 as strt Point
END
ELSE
Set @TimeChg = '4/1/' + Cast(Year(@DateUse) as varchar) --Use April 1 as start point
Set @DayVal = DatePart(weekday,@TimeChg) --Is the Start Point a Sunday
If @DayVal <> 1 --If Not then when is the proper Time Change Sunday
If DatePart(m,@TimeChg) = 4 --Is the base the April Value
Begin
Set @TimeChg = DateAdd(d,8 - @DayVal,@TimeChg) --For April it is the first Sunday of the Month
Set @TimeChg = DateAdd(hh,7, @TimeChg)
End
Else
BEGIN
Set @TimeChg = DateAdd(d,-@DayVal + 1,@TimeChg) --For Oct it is the last Sunday of the Month
Set @TimeChg = DateAdd(hh,6, @TimeChg)
END
If DatePart(m,@TimeChg) = 4 --If Time Change is Based on April
Begin
If @DateUse < @TimeChg --If DateUse < The Time Change Date & Time
Begin
Set @DateUse = DateAdd(hh,@ST,@DateUse) --Standard Time slide
End
Else
Set @DateUse = DateAdd(hh,@DST,@DateUse) --Daylight Saving Time slide
End
Else
If @DateUse < @TimeChg --If DateUse < The Time Change Date & Time
Begin
Set @DateUse = DateAdd(hh,@DST,@DateUse) --Daylight Saving Time slide
End
Else
Set @DateUse = DateAdd(hh,@ST,@DateUse) --Standard Time slide
PRINT @DateUse --Date time to eastern TZNote: One thing to note is the conversion between 1AM and 2AM on the last sunday of October cannot work right without knowing for sure which way the slide should go as the hour repeats for fall back.
--This Code Converts Local Time to GMT
Declare @DateUse DATETIME
Declare @TimeChg DATETIME
Declare @DayVal NUMERIC
Declare @DST tinyint
Declare @ST tinyint
SET @DST = 4-- GMT 4 hours ahead of Eastern
SET @ST = 5 -- GMT 5 hours ahead of Eastern
SET @DateUse = '10/12/2003' --Date time value to be changed. Fix your time here.
If Month(@DateUse) >= 10 --Check To See if Date is Oct, Nov, or Dec
BEGIN
Set @TimeChg = '10/31/' + Cast(Year(@DateUse) as varchar) --Use Oct 31 as strt Point
END
ELSE
Set @TimeChg = '4/1/' + Cast(Year(@DateUse) as varchar) --Use April 1 as start point
Set @DayVal = DatePart(weekday,@TimeChg) --Is the Start Point a Sunday
If @DayVal <> 1 --If Not then when is the proper Time Change Sunday
If DatePart(m,@TimeChg) = 4 --Is the base the April Value
Begin
Set @TimeChg = DateAdd(d,8 - @DayVal,@TimeChg) --For April it is the first Sunday of the Month
End
Else
Set @TimeChg = DateAdd(d,-@DayVal + 1,@TimeChg) --For Oct it is the last Sunday of the Month
Set @TimeChg = DateAdd(hh,2,@TimeChg) --Alaways Happens at 2 am
If DatePart(m,@TimeChg) = 4 --If Time Change is Based on April
Begin
If @DateUse < @TimeChg --If DateUse < The Time Change Date & Time
Begin
Set @DateUse = DateAdd(hh,@ST,@DateUse) --Standard Time slide
End
Else
Set @DateUse = DateAdd(hh,@DST,@DateUse) --Daylight Saving Time slide
End
Else
If @DateUse < @TimeChg --If DateUse < The Time Change Date & Time
Begin
Set @DateUse = DateAdd(hh,@DST,@DateUse) --Daylight Saving Time slide
End
Else
Set @DateUse = DateAdd(hh,@ST,@DateUse) --Standard Time slide
PRINT @DateUse --Date converted to GMT
--End GMT Code -
quote:
These are the bits I use in either sp or udf, I did have a calculate bit I built but cannot find right now. I had been working on a way to get it based on user input difference but did not finish.That's kind of provincial...
As I hinted, the transition dates and times vary from time zone to time zone (actually, it is more like country to country).
--Jonathan
--Jonathan -
You are absolutely right about the difference from country to country and in many cases they even just declare it as needs be (I had a site about the rules somewhere but cannot find link). But in the use it only varies by timezone so as to when it occurrs is easy to figure out. Then you just plug in the right figures for the associated timezone. For example Eastern in the US is 4 hours DST and 5 ST difference. Central would be 5 and 6 and so on. So for the US we can easily figure it out. Other than that you either have to be able to calc the rules, get people to accept you are using UTC, or your just out of luck.
-
-
Sorry that I'm coming into this a little late, but why don't you store the dates in UTC format. Then create a table that has the timezone definitions like below (no guarantee on syntax)
CREATE TABLE TimeZone
(
TimeZoneId INT,
TimeZoneName NVARCHAR(25),
TimeZoneOffset INT,
Year INT,
DaylightSavingsStart DATETIME,
DaylightSavingsEnd DATETIME
)
You'd have to then create two UDFs.
The first would be used to take any date and timezone, would extract the year, look to see if the date fell within the Start/End dates for the daylight savings, then do a DATEADD to get the UTC datetime.
The second would take a UTC date and timezone, would extract the year, look to see if the date fell within the Start/End dates for the daylight savings period, then do a DATEADD to get the local datetime.
Sounds like the offset should be a parameter for the sp because if the company / system goes more international more offsets may become relavant.
On second thought, just leave UTC in the reports and make the users mentally convert. 
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply