How to deal with time storage in SQL

Question

I have a web application that I'm writing (C#, MSSQL) and I need to store the timestamp when a record is stored in the system. Normally, I would just do this with SQL and the DATETIME function. However, the server is located in a different time zone than our company is located... AND we may change to another server in completely different time zone. The hosting company will not change the server time to match our local time zone. (Not that I blame them, but it was one thing I tried.)

So, my question is, what is the best way to store the date/time of the record update and what is the best way to present that date/time back to the user in our local time zone?

I want the least messy way of doing this, so a combo of C# and SQL would be fine as long as the solution is easy to implement. (My normal style is to do more work in stored procedures and less in C#, if that matters.)

Can you show me some sample code? Thanks!

Answer 1

Save UTC times in your database and localize those times every time you wanna show them to a user

Answer 2

always store DATETIME data in Universal Time Coordinated (UTC aka GMT)

• this avoids all timezone issues
• this avoids daylight-savings-time issues
• this allows simple date math to always work
• this allows transactions in different time zones to stay in synch
• this allows you to move the data to a different server in a different time zone and not screw everything up
• etc. "Universal" time, make sense?

C# DateTime provides DateTime.UtcNow and ToLocalTime(), SQL provides GETUTCDATE(). Here's a SQL function to convert UTC to local time -

-- convert UTC to local time
create FUNCTION [dbo].[udfUtcToLocalTime]
(
    @gmt datetime
)
RETURNS datetime
AS
BEGIN
    DECLARE @dt datetime
    SELECT 
        @dt = dateadd(millisecond,datediff(millisecond,getutcdate(), getdate()),@gmt)
    RETURN @dt
END

example:

SELECT dbo.udfUtcToLocalTime(someDateTimeField)
FROM someTable

Answer 3

Store it as UTC time and then preform the time zone calculation on the client end in C# use .ToUniversalTime() on the DateTime object to get the UTC time, store that on the server and then use .ToLocalTime() to convert it back.