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
goodkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Only the UTC types are correct, so SYSUTCDATETIME() or GETUTCDATE() is to be used. SYS* have a higher precision (if the server OS platform/hardware supports that).
goodkAuthor Commented:
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.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
"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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

David Johnson, CD, MVPOwnerCommented:
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
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
That is correct, but exactly the transformation on client is the issue.
goodkAuthor Commented:
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?
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.
sarabandeCommented:
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
Prakash SamariyaIT ProfessionalCommented:
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

PortletPaulEE Topic AdvisorCommented:
mmmmm, in most timesheet systems I've seen, you don't alter the time for display within a timesheet

so: "worked between 2pm to 4pm in Dallas"

is seen as 2pm to 4pm in the context of that timesheet (e.g. even if approving that timesheet from NY)

but once outside the timesheet context then you might shift the date/times for timezone
goodkAuthor Commented:
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?
Prakash SamariyaIT ProfessionalCommented:
I am using asp.net and sql server.

How do I generally show the date to the customers?
goodK, 1. You can get the client timezone using javascript/ajax call
//Get client timezone from javascript
function GetClientTimeZone(){
      var d = new Date()
      var timezoneOffset = d.getTimezoneOffset();
      return $http({
            url: '/GetClientTimeZone.aspx?tzOffset=' + timezoneOffset,
            method: 'GET',async: true,cache: false
      });
}

2. Now you have to write codebehind "GetClientTimeZone.aspx" to get client timezone; you can get using Request
Request["tzOffset"]

3. Once you have client timezone, you can use step 2 described by me in previous comment (modified code below)
TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById(tzOffset);
DateTime localDatetime = TimeZoneInfo.ConvertTimeFromUtc(yourUTCDateTime, tz);

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
goodkAuthor Commented:
Thank you guys
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Server Apps

From novice to tech pro — start learning today.