SQLServerCentral / Discuss Content Posted by Steve Jones / Article Discussions / Article Discussions by Author / Time Zone / Latest Posts

RE: Time Zone

marie christine.ouimet — Mon, 06 Aug 2012 12:41:32 GMT

Ok, I'm late...just went through a few old QOD and found this one.But I don't really agree with the answer. datetimeoffset doesn't give me timezone information. It just tells me what was the offset at a given date and time. That doesn't tell me what timezone I was in and doesn't even tell me if daylight savings was involved at that moment.I just wish we could have real timezone info in SQL Server one day. Would make reporting so much easier instead of having to store in UTC, then loop through all dates on the client side to convert to a specific timezone.See this editorial from Steve Jones [url=http://www.sqlservercentral.com/articles/Editorial/92067/]http://www.sqlservercentral.com/articles/Editorial/92067/[/url]

RE: Time Zone

BudaCli — Thu, 02 Aug 2012 07:11:57 GMT

Never heared of timezones :cool:

RE: Time Zone

Dhruvesh Shah — Tue, 24 Jan 2012 16:26:57 GMT

Nice discussion to follow up with the question.

RE: Time Zone

Koen Verbeeck — Tue, 24 Jan 2012 02:40:14 GMT

Nice easy question, thanks.

RE: Time Zone

mtassin — Tue, 10 Jan 2012 12:15:29 GMT

[quote][b]cengland0 (1/10/2012)[/b][hr][quote][b]mtassin (1/10/2012)[/b][hr]Oracle supports storing timezone with dates.http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions179.htmUsing the portability defense really isn't a good idea... each DB server is different enough, that queries that can run on all of them, likely run poorly on all of them.[/quote] I cannot disagree with this. However, would you rather write 3 or 4 versions of a program or just one? I sell a service and the companies I sell it to have a variety of server implementations. It might be okay to write 3 versions of the same application but then it becomes a problem with enhancements. You need to enhance all of them at the same time and that triples your workload.[/quote]MySQL, Orace, and SQL all support something akin to a stored procedure these days, why write three different apps? Put the SQL in the database. Then you only need an intelligent installation routine that runs the correct database (or tablespace) creation scripts depending on the platform.

RE: Time Zone

Steve Jones - SSC Editor — Tue, 10 Jan 2012 11:36:15 GMT

[quote][b]cengland0 (1/10/2012)[/b][hr][quote][b]mtassin (1/10/2012)[/b][hr]Oracle supports storing timezone with dates.http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions179.htmUsing the portability defense really isn't a good idea... each DB server is different enough, that queries that can run on all of them, likely run poorly on all of them.[/quote] I cannot disagree with this. However, would you rather write 3 or 4 versions of a program or just one? I sell a service and the companies I sell it to have a variety of server implementations. [/quote]If you are a software writer that has multiple targets, I can see your point. However the vast majority of us, 90+% of the developers out there write for applications that are not sold, are internal, and target one platform.Your comment is valid, but you should qualify it with the note that your job requires this. I have almost never seen anyone port code from one RDBMS to another, outside of an ISV. Even when we've switched platforms, it was a data move, not a code move.For ISVs, hire more people. Don't write queries that suck on all platforms. Rewrite them for different platforms.

RE: Time Zone

Rob Schripsema — Tue, 10 Jan 2012 10:31:24 GMT

A timely question. We're starting to have to deal with lots of time related data coming in from multiple time zones and need to come up with a good strategy for storing/comparing/managing it all. This data type will likely fill the bill for us, so this question is a good start on my research and design. Thanks!

RE: Time Zone

L' Eomot Inversé — Tue, 10 Jan 2012 10:20:26 GMT

[quote][b]SQLRNNR (1/9/2012)[/b][hr]EZ PZ[/quote]+1

RE: Time Zone

DBA_Dom — Tue, 10 Jan 2012 09:59:55 GMT

Nice easy question after a day full of issue, bugs and headaches. Thanks.

RE: Time Zone

OzYbOi d(-_-)b — Tue, 10 Jan 2012 09:49:54 GMT

tks Steve

RE: Time Zone

cengland0 — Tue, 10 Jan 2012 09:45:26 GMT

[quote][b]mtassin (1/10/2012)[/b][hr]Oracle supports storing timezone with dates.http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions179.htmUsing the portability defense really isn't a good idea... each DB server is different enough, that queries that can run on all of them, likely run poorly on all of them.[/quote] I cannot disagree with this. However, would you rather write 3 or 4 versions of a program or just one? I sell a service and the companies I sell it to have a variety of server implementations. It might be okay to write 3 versions of the same application but then it becomes a problem with enhancements. You need to enhance all of them at the same time and that triples your workload.

RE: Time Zone

Revenant — Tue, 10 Jan 2012 09:30:13 GMT

I am surprised that only 62 percents of respondents got this one right.Thanks, Steve!

RE: Time Zone

mtassin — Tue, 10 Jan 2012 09:29:05 GMT

Oracle supports storing timezone with dates.http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions179.htmUsing the portability defense really isn't a good idea... each DB server is different enough, that queries that can run on all of them, likely run poorly on all of them.

RE: Time Zone

cengland0 — Tue, 10 Jan 2012 09:17:44 GMT

Call me old fashioned but I would use two columns for this too. The big reason is that I like using portable SQL code and this is new to SQL Server 2008. If I put my code on another company's server and they are using 2005, then it will stop functioning. Why should I subject myself to emergency repairs if I can fix the problem during design time?I also like writing SQL code that is compatible with Oracle and MySQL. Many of the datetime functions make this difficult but at least I try my best to keep it standardized by using as much ANSI SQL as possible.

RE: Time Zone

codebyo — Tue, 10 Jan 2012 09:04:51 GMT

Such an easy question that feels refreshing and points out an excellent feature for international dates.Thank you, Steve, Hugo and Mark for the additional explanations and opinions.Best regards,

RE: Time Zone

mtassin — Tue, 10 Jan 2012 08:56:43 GMT

It is also nice when you want to look at all the values relative to your current timezone. For instance, all the logging data goes into the table from the various servers in their timezone. Well, I have trouble thinking about what time 14:00:00 +7:00 is, but I can just do SWITCHOFFSET(-6*60,<datetimefield>) and have it in my time zone.SWITCHOFFSET is a very nice function when it comes to DATETIMEOFFSET and I wish we'd had it and DATETIMEOFFSET as far back as SQL 6. The oddball things I have to do to work with datetime data and handle things like Daylight savings time, and timezone variations magically go away for me with DATETIMEOFFSET.

RE: Time Zone

Cliff Jones — Tue, 10 Jan 2012 07:43:15 GMT

Good question. This is useful.

RE: Time Zone

Steve Jones - SSC Editor — Tue, 10 Jan 2012 07:26:32 GMT

[quote][b]tommyh (1/9/2012)[/b][hr]I have never needed to store information about Timezones so excuse me if this is a stupid question. But wouldnt it be easier to store this in a separate column? I mean lets say i use this for say logging and we for some reason need to find all entries in a specific timezone. If you have 2 columns thats easy but with this datatype... how would you do that? (got no 2008 db to test on so :()/T[/quote]Hugo has pointed out the best reason, it also allows you to easily compare or work with all the rows in that column. If you have people entering data from different time zones, this allows you to easily store all the data in a correct chronological fashion, and the clients can easily convert to their local time without an issue.

RE: Time Zone

Mike Is Here — Tue, 10 Jan 2012 05:58:00 GMT

Nice and easy.... and useful.

RE: Time Zone

Britt Cluff — Tue, 10 Jan 2012 05:48:28 GMT

Yeah, another semi-easy point. Thanks for submitting.

RE: Time Zone

Hugo Kornelis — Tue, 10 Jan 2012 00:50:16 GMT

The benefit of storing it in a single column is that you can now easily compare two time stamps. With two columns, questions such as "is Jan 10 2012, 8:43 +01:00 earlier or later than Jan 9 2012, 23:50 -08:00?". You can even calculate how far these two time stamps are apart (7 minutes) by using the DATEDIFF function.

RE: Time Zone

tommyh — Mon, 09 Jan 2012 23:33:57 GMT

I have never needed to store information about Timezones so excuse me if this is a stupid question. But wouldnt it be easier to store this in a separate column? I mean lets say i use this for say logging and we for some reason need to find all entries in a specific timezone. If you have 2 columns thats easy but with this datatype... how would you do that? (got no 2008 db to test on so :()/T

RE: Time Zone

bitbucket-25253 — Mon, 09 Jan 2012 22:09:46 GMT

Easy QODs at the beginning of the week ... now what confusing, complex, obscure QOD will Steve post for Friday ?

RE: Time Zone

SQLRNNR — Mon, 09 Jan 2012 21:22:19 GMT

EZ PZ

Time Zone

Steve Jones - SSC Editor — Mon, 09 Jan 2012 21:22:02 GMT

Comments posted to this topic are about the item [B]Time Zone[/B]