Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: Datetime with timezone information in it?

Show all headers | 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