Link to home
Start Free TrialLog in
Avatar of goodk
goodkFlag for United States of America

asked on

Ok, if my app is to be used in different time zones. What is the best way to store date time?

defaults timestamps and interaction log timestamps.  Such that there would be no issues during daylight savings time change.  I am using MS sql server and asp.net.


SELECT SYSDATETIME()
    ,SYSDATETIMEOFFSET()
    ,SYSUTCDATETIME()
    ,CURRENT_TIMESTAMP
    ,GETDATE()
    ,GETUTCDATE();


Any article on this will be helpful
SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of goodk

ASKER

What is datatype timestamp in sql server.  Is it also UTC?

So when I display the time stored in UTC how do i stay correct according to the time zone?  Is there any built in function?  Or is it automatic?

Please give an example- thank you.
"Timestamp" is a number just incrementing system-wide. It has no direct relation to time or date. The ANSI SQL type name is RowVersion, and only used to check for changes to a row. A field of this type gets updated automatically if the row is changed.
datetime objects are stored with the time reference of UTC time when they are displayed in human readable format they use the users culture information and do the conversion to local time
That is correct, but exactly the transformation on client is the issue.
Avatar of goodk

ASKER

If an employee enters their timesheet as worked between 2pm to 4pm in Dallas and their supervisor look at the same timesheet in California would they see 2PM to 4PM or 12PM to 2PM?


Ideally the supervisor should see 12PM to 2PM.  

Do I need to do anything to the page or just a simple display would keep everything correct according to their local time?
In that case you would store getdate(). But you have to make sure it is used at the client, respective with correct time zone info and manual compensation.
If you talk about a web page, the web server determines the time zone info. You can see that issue here on EE, where some time stamps are in Pacific Time, some are using your timezone info from the profile. EE site cannot really know your timezone, and has to rely on your profile data.
Ideally the supervisor should see 12PM to 2PM.

rarely, if he wants to know whether the employee worked in the afternoon shift or morning shift.

if you look at a time you always need to regard the context of the time whether it is a global context or local context. so you store in utc and show local as told by David. that also can mean that you are in LA but see NY time.

Sara
I hope you are keeping/storing users timezone related things somewhere from which we can access it!  Now we have to do following things

1. Stores the datetime data by converting into UTC like
DateTime dt = DateTime.Now;
dt.ToUniversalTime();

Open in new window

2. When we read value from database, now again we need to convert datetime value into Local timezone
TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById("<W. Europe Standard Time>");
//"<W. Europe Standard Time>" - users timezone 
DateTime localDatetime = TimeZoneInfo.ConvertTimeFromUtc(yourUTCDateTime, tz);

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of goodk

ASKER

I am still confuse on this subject.  That is why I was asking if there is an article on all aspects of time pertaining to global application and day light savings time.  

Saving data in the database in UTC is an easy step.

I am using asp.net and sql server.

How do I generally show the date to the customers?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of goodk

ASKER

Thank you guys