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

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

Re: Datetime with timezone information in it?

Show all headers | View raw

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

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