1

I'm looking for the best practice solution.

Take for example stackoverflow and a question I asked :

I live in Israel , and now the time is :

enter image description here

I did ask something at ~17:58 ( 3 hours before now)

enter image description here

Yet , it does show "3 hours ago" AND display UTC time . ( Israel is +2)

So either it saves both UTC and my offset

or it saves just UTC and in JS at client side it showes me the local time.

now my question is from a DB point of view :

I can detect from where the client has connected via JS command : 

new Date().getTimezoneOffset(); //-120

I could store it like : 

data  |    UTCTime          | userOffset
----------------------------------------
 ...    2012-12-24 15:36:31       -2

Question

  • Is this the best solution of storing world wide events (datetimes) ?

  • In c# there is a DateTimeOffset Structure type which store the offset inside it . is there any equivalent solution in Sql server ?

Royi Namir
  • 131,490
  • 121
  • 408
  • 714
  • 2
    Related: http://stackoverflow.com/questions/2532729/daylight-saving-time-and-timezone-best-practices – Oded Dec 24 '12 at 19:10
  • @Oded but there is a problem. it says _"always keep your date unified"_ but it also says - "_avoid js calculation_" so how did Stackoverflow knows to show me the local datetime ? it has to know my offset . and this is done by either GEOIP service or JS.I would like to know what is wrong with my solution above. – Royi Namir Dec 24 '12 at 19:14
  • @RoyiNamir, that doesn't mean it has to be done in JS. The trip to the server, that retrieved all the data, would have modified the time to show the right "offset" as far as "how long ago was this asked." Clearly it's showing the right UTC time - but it knows that based on where you are - that's `3 hours ago`. – Mike Perrenoud Dec 24 '12 at 19:23
  • It doesn't do that. It uses the JavaScript `Date` object that will by default give the computer date it is running on (with offset). This is calculated against the UTC date posted on the page. – Oded Dec 24 '12 at 19:23
  • @Oded, thanks for the clarification, just learned another little tidbit for future projects! – Mike Perrenoud Dec 24 '12 at 19:24
  • @Oded So I was right ? it saves the utc but the client exposed the offset...right ? – Royi Namir Dec 24 '12 at 19:25
  • 1
    Yes. That's how it is done on the Stack Exchange sites. If you view the HTML source, you will see the UTC timestamp (usually held in `title` attributes). They simply calculate an offset in JS and replace a div contents with a string. No server side involvement. – Oded Dec 24 '12 at 19:28
  • @Oded please have a look at MarcinJuraszek's answer below.(his second comment) he suggest something interesting. he says that the "3 hours ago " is the result of the `rendered page time (utc)` minus `saved post utc time`. so here I dont need the offset at all. please explain ? – Royi Namir Dec 24 '12 at 19:31

2 Answers2

2

According to this post: you should always keep your date unified. So UTC would be the best choice.

On the other hand - when you have date in UTC you can easily show it right according to someones timezone (not only for the author, but for everybody).

Community
  • 1
  • 1
MarcinJuraszek
  • 118,129
  • 14
  • 170
  • 241
  • see my response to Oded (as a comment) – Royi Namir Dec 24 '12 at 19:14
  • The answer is really simple - offset is always the same, no matter which timezone you use :) It can be calculated using current UTC time, because post time is in UTC as well. – MarcinJuraszek Dec 24 '12 at 19:18
  • Do you realize that it showed me the UTC ( which is fine) but also my local time ? it knew i'm from israel . and besided - offset is not always the same.never. – Royi Namir Dec 24 '12 at 19:22
  • IT showed you '3 hours ago', which could be calculated using UTC time (18:48 - 15:36) – MarcinJuraszek Dec 24 '12 at 19:23
  • Do you mean that the "3 hours ago " is the result of the `rendered page time (utc)` minus `saved post utc time`. so here I dont need the offset at all......? – Royi Namir Dec 24 '12 at 19:44
  • exactly. You don't need anything more that two datetimes (current and saving time) from the same timezone. It can be just server time, so it doesn't have to be your local timezone. – MarcinJuraszek Dec 24 '12 at 19:51
1

Yes. MS SQL Server 2008 introduced datetimeoffset.

Here you can find all datetime types.

And here on how to use them.

Paul Fleming
  • 22,772
  • 8
  • 65
  • 107