Time Zone

Question

Time Zone

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
January 9, 2012 at 9:22 pm

#74240

Comments posted to this topic are about the item Time Zone

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

EZ PZ

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

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

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

tommyh SSCertifiable Points: 6302 More actions · Answer 3

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

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 4

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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 5

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

http://brittcluff.blogspot.com/

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 6

Mike Is Here

Hall of Fame

Points: 3348

January 10, 2012 at 5:58 am

#1431039

Nice and easy.... and useful.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 7

tommyh (1/9/2012)
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

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.

Cliff Jones SSChampion Points: 10547 More actions · Answer 8

Cliff Jones

SSChampion

Points: 10547

January 10, 2012 at 7:43 am

#1431161

Good question. This is useful.

mtassin SSC-Insane Points: 23099 More actions · Answer 9

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.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 10

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,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

cengland0 SSCertifiable Points: 6102 More actions · Answer 11

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.

mtassin SSC-Insane Points: 23099 More actions · Answer 12

Oracle supports storing timezone with dates.

http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions179.htm

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

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

Revenant SSC-Forever Points: 42467 More actions · Answer 13

I am surprised that only 62 percents of respondents got this one right.

Thanks, Steve!

cengland0 SSCertifiable Points: 6102 More actions · Answer 14

mtassin (1/10/2012)
Oracle supports storing timezone with dates.
http://docs.oracle.com/cd/B14117_01/server.101/b10759/functions179.htm
Using 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.

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.