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.
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.
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! :)))