Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

The SQL Server 2008 Datetimeoffset Data Type Expand / Collapse
Author
Message
Posted Sunday, April 17, 2011 11:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
Comments posted to this topic are about the item The SQL Server 2008 Datetimeoffset Data Type

James
MCM [@TheSQLPimp]
Post #1094715
Posted Monday, April 18, 2011 7:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411

•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)?




--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1094947
Posted Monday, April 18, 2011 7:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
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]
Post #1094968
Posted Monday, April 18, 2011 8:33 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 361, Visits: 840
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.


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #1095017
Posted Monday, April 18, 2011 9:16 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 18, 2014 5:57 PM
Points: 535, Visits: 729
Thank you for the article! I didn't even know this feature existed I suppose it wasn't publicized enough. It's incredibly useful, but, as Kendal pointed out, handling DST changes remains a challenge.


Post #1095063
Posted Monday, April 18, 2011 9:39 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, April 04, 2014 7:39 AM
Points: 59, Visits: 337
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]
Post #1095086
Posted Monday, April 18, 2011 11:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 11:12 AM
Points: 2, Visits: 4
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'
Post #1095189
Posted Monday, April 18, 2011 11:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 1:56 PM
Points: 361, Visits: 840
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.


Kendal Van Dyke
http://kendalvandyke.blogspot.com/
Post #1095201
Posted Monday, April 18, 2011 12:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 11:12 AM
Points: 2, Visits: 4
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.
Post #1095216
Posted Monday, April 18, 2011 1:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
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)






--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #1095272
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse