April 17, 2011 at 11:13 pm
Comments posted to this topic are about the item The SQL Server 2008 Datetimeoffset Data Type
James
MCM [@TheSQLPimp]
April 18, 2011 at 7:41 am
•Datetimeoffset whenever the data will refer to an exact point in time - transaction/added/updated datetimes (UTC) and localized datetimes.
•Datetime for any cases where the time is irrelevant across time zones or the timezone is simply not known - imported data, standard datetimes that are the same across timezones, etc.
•Datetime with a 00:00:00 time to represent whole dates - dates of birth
•Time for just times of the day where the date is irrelevant - opening hours, etc.
Since you're using Time for the time of day where date is irrelevant,
Why are you not using the Date data type for Dates where time is irrelevant (such as dates of birth)?
April 18, 2011 at 7:55 am
Good point Mark, Date would be better here as the time portion is irrelevant. I don't tend to store many dates like this but the Date data type would be preferred over Datetime.
James
MCM [@TheSQLPimp]
April 18, 2011 at 8:33 am
One downside of datetimeoffset is that it's not easy to switch to different timezones without understanding the nuances of Daylight Saving Time (though to be fair it's not easy with any other datetime format in SQL Server either).
For example, if I want to convert both 2011-03-12 07:30:00.0000000 +00:00 and 2011-03-14 07:30:00.0000000 +00:00 to Eastern time I need to understand that the offset for the 12th is 5 hours but the offset for the 14th is 4 hours. And what about geographic areas that don't observe DST? Does a -7 hour offset represent Phoenix at an time in the year or Seattle only during DST?
It's unfortunate that system functions for timezone awareness were not included with the introduction of datetimeoffset, especially considering that SQL Server has the spatial data type and this is something that's built into .NET, Java, and most other platforms\languages. It would be incredibly useful to supply a datetime value, a spatial value (or a timezone name), and get a datetimeoffset with the correct offset in return.
Erland Sommarskog submitted a Connect feature request for this (http://connect.microsoft.com/SQLServer/feedback/details/293933/add-a-set-timezone-command). Please consider voting for it to be included in a future version of SQL Server.
April 18, 2011 at 9:16 am
Thank you for the article! I didn't even know this feature existed :blush: I suppose it wasn't publicized enough. It's incredibly useful, but, as Kendal pointed out, handling DST changes remains a challenge.
April 18, 2011 at 9:39 am
Thanks all, I too was unaware of this data type but have now put it into use. I agree that DST is a pain. The timezones in .net know that they are DST affected but still return the time without this factored in. Nice. As I say, I stumbled across this and it saved me a whole heap of pain but Microsoft do need to handle the DST issue or else we will still have to code around this in some way.
James
MCM [@TheSQLPimp]
April 18, 2011 at 11:40 am
This is what I came up with, it converts the start time from UTC to whatever the local time is running on the sql server it is dynamic and should work on any sql server in any timezone. What is nice is when the server goes to DST this will also return the DST time.
Print CONVERT(char(10), dateadd( hh, datepart(hour, GetDate()-GetUTCDate() ) - 24, '2011-04-22 17:30:00.000') , 108) as 'Activity Start Time'
April 18, 2011 at 11:57 am
vreten (4/18/2011)
This is what I came up with, it converts the start time from UTC to whatever the local time is running on the sql server it is dynamic and should work on any sql server in any timezone. What is nice is when the server goes to DST this will also return the DST time.Print CONVERT(char(10), dateadd( hh, datepart(hour, GetDate()-GetUTCDate() ) - 24, '2011-04-22 17:30:00.000') , 108) as 'Activity Start Time'
This works if the date you're looking at is for an event that occurs in the same time zone as the server and does not cross the boundry between the start and end dates & times for DST.
A scenario where the problem still exists is let's say today I want to convert an Eastern time zone date from Jan 1, 2011 to UTC. Today's offset is -4 hours but the proper offset for the date I'm looking at is -5 hours. Using the current offset for the server when I analyze the date would mean my conversion is off by an hour.
April 18, 2011 at 12:29 pm
Kendal Van Dyke (4/18/2011)
vreten (4/18/2011)
This is what I came up with, it converts the start time from UTC to whatever the local time is running on the sql server it is dynamic and should work on any sql server in any timezone. What is nice is when the server goes to DST this will also return the DST time.Print CONVERT(char(10), dateadd( hh, datepart(hour, GetDate()-GetUTCDate() ) - 24, '2011-04-22 17:30:00.000') , 108) as 'Activity Start Time'
This works if the date you're looking at is for an event that occurs in the same time zone as the server and does not cross the boundry between the start and end dates & times for DST.
A scenario where the problem still exists is let's say today I want to convert an Eastern time zone date from Jan 1, 2011 to UTC. Today's offset is -4 hours but the proper offset for the date I'm looking at is -5 hours. Using the current offset for the server when I analyze the date would mean my conversion is off by an hour.
Yeah, I had the opposite problem, UTC in the DB that needed to be converted the local timezone. It would be cool if GetDate could be overriden to accept standard or daylight parameter and perform the offset automatically.
April 18, 2011 at 1:48 pm
Make a table...
CREATE TABLE [Daylightsavings](
[PK_dst_key] [int] IDENTITY(1,1) NOT NULL,
[dst_year] [int] NULL,
[dst_begin] [date] NULL,
[dst_end] [date] NULL,
[dst_diff] [int] NULL,
PRIMARY KEY CLUSTERED
(
[PK_dst_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT the daylight savings start and stop times plus the offset to use.
insert into Daylightsavings
(dst_year,dst_begin,dst_end,dst_diff)
values
(1980,'1980-04-27','1980-10-26',1),
(1981,'1981-04-26','1981-10-25',1),
(1982,'1982-04-25','1982-10-31',1),
(1983,'1983-04-24','1983-10-30',1),
(1984,'1984-04-29','1984-10-28',1),
(1985,'1985-04-28','1985-10-27',1),
(1986,'1986-04-27','1986-10-26',1),
(1987,'1987-04-05','1987-10-25',1),
(1988,'1988-04-03','1988-10-30',1),
(1989,'1989-04-02','1989-10-29',1),
(1990,'1990-04-01','1990-10-28',1),
(1991,'1991-04-07','1991-10-27',1),
(1992,'1992-04-05','1992-10-25',1),
(1993,'1993-04-04','1993-10-31',1),
(1994,'1994-04-03','1994-10-30',1),
(1995,'1995-04-02','1995-10-29',1),
(1996,'1996-04-07','1996-10-27',1),
(1997,'1997-04-06','1997-10-26',1),
(1998,'1998-04-05','1998-10-25',1),
(1999,'1999-04-04','1999-10-31',1),
(2000,'2000-04-02','2000-10-29',1),
(2001,'2001-04-01','2001-10-28',1),
(2002,'2002-04-07','2002-10-27',1),
(2003,'2003-04-06','2003-10-26',1),
(2004,'2004-04-04','2004-10-31',1),
(2005,'2005-04-03','2005-10-30',1),
(2006,'2006-04-02','2006-10-29',1),
(2007,'2007-03-11','2007-11-04',1),
(2008,'2008-03-09','2008-11-02',1),
(2009,'2009-03-08','2009-11-01',1),
(2010,'2010-03-14','2010-11-07',1),
(2011,'2011-03-13','2011-11-06',1),
(2012,'2012-03-11','2012-11-04',1),
(2013,'2013-03-10','2013-11-03',1),
(2014,'2014-03-09','2014-11-02',1),
(2015,'2015-03-08','2015-11-01',1),
(2016,'2016-03-13','2016-11-06',1),
(2017,'2017-03-12','2017-11-05',1),
(2018,'2018-03-11','2018-11-04',1),
(2019,'2019-03-10','2019-11-03',1),
(2020,'2020-03-08','2020-11-01',1),
(2021,'2021-03-14','2021-11-07',1),
(2022,'2022-03-13','2022-11-06',1),
(2023,'2023-03-12','2023-11-05',1),
(2024,'2024-03-10','2024-11-03',1),
(2025,'2025-03-09','2025-11-02',1)
Now JOIN to this table
LEFT JOIN Daylightsavings dst ON
<somedatevalue> between dst.begin and dst.end
Then use isnull
somedatevalue = dateadd(hh,isnull(dst.dst_diff,0),somedatevalue)
April 18, 2011 at 3:25 pm
mtassin (4/18/2011)
Make a table....
.
.
INSERT the daylight savings start and stop times plus the offset to use.
.
.
.
Now JOIN to this table
.
.
.
Of course if you operate in more than one country (or even in more than one state in Australia - don't know if everywhere in the US has the same DST rules), this table would get way more complicated where you have different places with different DST rules.... You'd have to add in a time zone name column too, which would then also need to get stored along with your datetimeoffset value in every table where you want to use the information - back to needing two columns to store your time data.
Having a 'DST' bit in the datetimeoffset type would be the best resolution. I would have thought they could add a bit quite easily - perhaps an extra bit of 'precision'.
April 18, 2011 at 9:17 pm
Thanks for the article. My only suggestion is that I think you might have made a stronger point by using 'hour' for your DATEDIFF examples as timezone differences are [usually] measured in hours. But otherwise I think you did good by raising awareness of this feature.
It's worth mentioning that another 2008 feature, Data Collection, stored its results in UTC time. This threw me at first until I dug into the schema and then BOL'd datetimeoffset. I'd much rather have known about this earlier. (I was writing my own reports, btw. Not meaning to suggest that the Data Collection reports themselves are UTC time.)
As to daylight savings time being a consideration for functions, I fear this would be too unwieldy. There's a lot of different start-end dates for these, including many 'one-off' examples (ie: the Sydney 2000 Olympics garnered an extended DS period for just that year) and it would be a job in itself to maintain that system.
Steve.
June 8, 2015 at 3:23 pm
James A Skipwith (4/18/2011)
Good point Mark, Date would be better here as the time portion is irrelevant. I don't tend to store many dates like this but the Date data type would be preferred over Datetime.
Most of our dates (and we have plenty) don't care about time, such as the date range that someone is covered for a particular service. The few cases where we use datetime are for fields like the last time the record was touched or when the record was created.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply