Path: csiph.com!usenet.pasdenom.info!goblin2!goblin.stu.neva.ru!fi.sn.net!newsfeed1.tdcnet.fi!news.song.fi!not-for-mail Newsgroups: comp.databases.ms-sqlserver Subject: Re: Datetime with timezone information in it? References: From: Antti =?us-ascii?Q?J=3D=3Fiso-8859-1=3FB=3F5A=3D=3D=3F=3Drvinen?= Organization: oreganozation Date: Fri, 02 Mar 2012 15:46:54 +0200 Message-ID: User-Agent: Gnus/5.1008 (Gnus v5.10.8) XEmacs/21.4.22 (linux) Cancel-Lock: sha1:kYrQ2kifD8nhNGucX8LubBFtvME= MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Lines: 32 NNTP-Posting-Host: 213.139.166.59 X-Trace: 1330696014 news.fv.fi 2824 213.139.166.59:53590 X-Complaints-To: abuse@news.fv.fi Xref: csiph.com comp.databases.ms-sqlserver:937 Erland Sommarskog 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