I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

Do you know when your data was modified? Or do you?

We're all familiar with four constant columns that should be in every table.
Those are: CreatedOn, CreatedBy, LastUpdatedOn, LastUpdatedBy or any other names you have for them. :))
But you get my point. Recently i'm leaning into a direction that there should be 2 more columns
added to those four. Those are CreatedOnTZOffset and LastUpdatedOnTZOffset where TZOffset stands Time Zone Offset.

Some may ask why do we need this? Well let's say you have an app that is accesed
from all over the world and you're not just displying data to users. They can
also add and update stuff. Time Zone information gives you a better
understanding when and from where your users are accessing data and any reports
that you might want also become easier. There are probably other uses too,
but that's on a case by case basis. :))

Usually those four original columns are populated either with Sql's GetDate() on the SQL server or
Now() in VBscript and DateTime.Now in C# in the "Front End app".

So what's the problem with that, you might ask? There isn't one.
It's all great, but GetDate() gives you SQL Servers local time based on it's regional settings.
So that's why we have Now() in the front end, right? Wrong!

What is front end app acctually?
For WinForms apps that's true it gives the local date of the machine on which the WinForms app is running.
For Web it's another story. Your front end is acctualy a web browser.
And Now() is used on the web server running IIS so Now() will return web server's local time.

Example:
1. SQL Server is in Slovenia
2. Web Server is in Russia
3. User browsing is in Australia.

Those are 3 different Timezones and with Now() and GetDate() only 1st or 2nd is coverd.

So, how to fix this?
Well SQL Server has this handy little function called GetUTCDate() that returns GMT = Greenwich Mean Time.

Getting client's timezone offset is also preety simple.
JavaScript has another handy little function called getTimezoneOffset().
It returns the difference between local time and GMT in minutes.
However this value is not a constant, because of the practice of using Daylight Saving Time,
but that isn't a huge problem if you ask me.

So all you need is to pass the TimeZone Offset in minutes to your sproc and use SQL Server's GetUTCDate().
I usually pass the timezone offset in a hidden field, but you could also pass it in the querystring,
save it in a cookie or in the session.

So having timezone info of your users can be a very helpfull thing. Use it! :)))

Print | posted on Sunday, May 21, 2006 5:37 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Do you know when your data was modified? Or do you?

Why not always store the UTCDate in all DateTime columns?

Thanks

Marco
5/21/2006 7:44 PM | Marco
Gravatar

# Interesting Finds: May 21, 2006 PM edition

5/21/2006 3:02 PM | Jason Haley
Gravatar

# re: Do you know when your data was modified? Or do you?

you tell me.... :))
i'm all for it...
5/21/2006 9:02 PM | Mladen
Gravatar

# re: Do you know when your data was modified? Or do you?

We store all of our times in GMT since we support multiple time zones. We then convert it to the user's time zone for display purposes. In one application, we store the user's time zone preference in a table. In another application, we grab the information from the client's PC.
5/22/2006 8:34 PM | Tara
Gravatar

# re: Do you know when your data was modified? Or do you?

if only everyone would think like you tara :)))
5/23/2006 8:55 AM | Mladen
Gravatar

# re: Do you know when your data was modified? Or do you?

Nicely statred. But its just a case of bad design to have this situation in the first place.
5/29/2006 7:14 PM | rudy
Gravatar

# re: Do you know when your data was modified? Or do you?

This is ridiculous database design. What happens when the user moves? Then all the time zone offsets would be irrelevant for those modified records. It should all be stored as UTC, and then displayed differently based on user settings like Tara said.
1/3/2008 8:52 PM | Matt
Gravatar

# re: Do you know when your data was modified? Or do you?

what is a ridicolous db design Matt?
and how is what i suggested different from what Tara does?

i never said for all datetime columns. i just said for CreatedOn and UpdatedOn.
i used timezone info on those two to see when the data was really modified, so utc + offset info. sometimes utc just isn't enough even for admin purposes and that has nothing to do with how users view their dates.
1/3/2008 9:02 PM | Mladen
Gravatar

# re: Do you know when your data was modified? Or do you?

WERY NİCE
5/30/2008 11:22 PM | oyun
Gravatar

# re: Do you know when your data was modified? Or do you?

All the solutions offered in the postbacks are OK but ONLY if it is not important to know the TimeZone in which the record was actually created. because saving the time in UTC only allows you to convert it to each user's time zone but you will never know again in what time zone the record was created.
8/4/2008 8:53 PM | John
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET