Groups > comp.databases.ms-sqlserver > #938

Re: Datetime with timezone information in it?

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 <esquel@sommarskog.se>
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	<XnsA00ABAF6736Yazorman@127.0.0.1> (permalink)
References	<m3d38v5r9t.fsf@muikku.katiska.org> <XnsA00A763728520Yazorman@127.0.0.1> <m3fwdrw1wx.fsf@muikku.katiska.org>
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

Show key headers only | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Datetime with timezone information in it? Antti J=?iso-8859-1?B?5A==?=rvinen <costello@iki.fi> - 2012-03-02 10:41 +0200
  Re: Datetime with timezone information in it? Erland Sommarskog <esquel@sommarskog.se> - 2012-03-02 10:37 +0000
    Re: Datetime with timezone information in it? Antti J=?iso-8859-1?B?5A==?=rvinen <costello@iki.fi> - 2012-03-02 15:46 +0200
      Re: Datetime with timezone information in it? Erland Sommarskog <esquel@sommarskog.se> - 2012-03-02 17:22 +0000
        Re: Datetime with timezone information in it? Justin <kfwolf@hotmail.com> - 2012-03-08 06:37 -0800

csiph-web