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

Groups > comp.databases.ms-sqlserver > #935 > unrolled thread

Datetime with timezone information in it?

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#935 — Datetime with timezone information in it?

Dear Sirs,

I tried briefly looking through docs but I could not find definite answer: 
is there database column datatype that would save date, time and the 
timezone as a single value? I have an app that is supposed to run 24/7 
and having those daylight saving timezone-changes occurring twice a 
year hoses up couple of things twice a year.

I still have the (good) option of saving time info as seconds since beginning
of year 1970 and having timezone conversions done at client end
but that's not that convenient -> what is the correct way to handle
this around with ms sql server (clients will be .net and asp.net thingies).
The DATETIMEOFFSET of 2008 version did not look too handy to me, 
anything else? 

-- 
Costello the Warrior        St:18/09 Dx:14 Co:18 In:8 Wi:12 Ch:7  Neutral
Dlvl:16 $:0  HP:129(129) Pw:52(52) AC:-6 Xp:14/83896 T:19462 Satiated

[toc] | [next] | [standalone]

#936

Antti Järvinen (costello@iki.fi) writes:
> I tried briefly looking through docs but I could not find definite answer: 
> is there database column datatype that would save date, time and the 
> timezone as a single value? I have an app that is supposed to run 24/7 
> and having those daylight saving timezone-changes occurring twice a 
> year hoses up couple of things twice a year.
> 
> I still have the (good) option of saving time info as seconds since
> beginning of year 1970 and having timezone conversions done at client
> end but that's not that convenient -> what is the correct way to handle 
> this around with ms sql server (clients will be .net and asp.net
> thingies). 
> The DATETIMEOFFSET of 2008 version did not look too handy to me, 
> anything else? 
 
What's wrong with datetimeoffset? It is not DST-aware in the sense
that if you do dateadd(MONTH, 6, sysdatetimeoffset()) you will get a 
value where the timezone part is +02:00, although you will be in 
+03:00 at that point. But apart from that it should work.


-- 
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

[toc] | [prev] | [next] | [standalone]

#937

Erland Sommarskog <esquel@sommarskog.se> writes:
> What's wrong with datetimeoffset? It is not DST-aware in the sense
> that if you do dateadd(MONTH, 6, sysdatetimeoffset()) you will get a 
> value where the timezone part is +02:00, although you will be in 
> +03:00 at that point. But apart from that it should work.

We do a lot tables that look like
|timestamp  | val1 | val2 | ...
+------------------------------
| 13:55:00  | 1.0  | 1.1  | ...
| 13:56:00  | 1.05 | 1.11 | ...
...

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 :)

Might be easiest to record 2 timestamps for each table, one in local TZ
and another in UTC ; UI would show one, queries use the other but that
again is a terrible mess.. don't know if I want that either.

-- 
Costello the Warrior        St:18/09 Dx:14 Co:18 In:8 Wi:12 Ch:7  Neutral
Dlvl:16 $:0  HP:129(129) Pw:52(52) AC:-6 Xp:14/83896 T:19462 Satiated

[toc] | [prev] | [next] | [standalone]

#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

[toc] | [prev] | [next] | [standalone]

#940

On Mar 2, 12:22 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Antti Järvinen (coste...@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:
>

Investigate getutcdate(); One option is two store two dates (Local
time and UTC).  We have colleagues in Brazil and when we spring
forward they fall back - so it does become challenging.

> 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, esq...@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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web