Path: csiph.com!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver Subject: Re: Datetime with timezone information in it? Date: Fri, 2 Mar 2012 17:22:40 +0000 (UTC) Organization: Erland Sommarskog Lines: 33 Message-ID: References: Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Date: Fri, 2 Mar 2012 17:22:40 +0000 (UTC) Injection-Info: mx04.eternal-september.org; posting-host="v1P300f0mDXpBZGzT/9PAw"; logging-data="4819"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX192YcvzrKzxkD3VaQv2C1VI" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:3YsElT2kg5OnnybGdqjQ643DNME= Xref: csiph.com comp.databases.ms-sqlserver:938 Antti Järvinen (costello@iki.fi) writes: > and a lot of queries meaning about "get values for val1 from last hour" > and with unix-timestamp that becomes "gimme rows where timestamp value > differs less than 3600 compared to current systime" that is > programmatically easy and doesn't care about timezone but as said, > having those integer values in database isn't that convenient for all db > clients we have. > > During those DST transition hours having no timezone in values of the DB > there is no way of knowing which particular row was inserted before DST > change and which after so the query results would then then happily mix > rows from last 2 hours (if clock was changed backwards) or return zero > rows at that moment where clock is turned forward :) Not if you use datetimeoffset. For instance, this example has two timestamps from the most recent night when Finland switch from DST back to regular time: select datediff(minute, convert(datetimeoffset, '20111030 02:34:30 +03:00'), convert(datetimeoffset, '20111030 02:24:30 +02:00')) The value is 50. Another alternative is to use getutcdate() to get the timestamp values. Then you don't need to store the time zone, nor bother about DST changes. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx