[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Retrieve System DateTime from User

Hello Experts,

I created an application in ASP.NET C# and I'm using SQL Server 2012 for my database.

I currently collect the users Date Time from SQL Server using the GetDate() Function. However, the website is hosted in the West Coast while I'm located in the East Coast and it's collecting the entry time in PST instead of EST.

Is there a workaround to this and if so what would be suggested?

Should I remove the GetDate() Function on the SQL Server Database and just collect the current users Date Time in code? If so, what is the best method to collect this in ASP.NET C# code?
0
asp_net2
Asked:
asp_net2
  • 8
  • 7
  • 2
1 Solution
 
Randy PooleCommented:
You can either continue to use the servers date/time then convert that value to the timezone for the client that way all timestamps are relative to a certain timezone, or you can pass the datetime to the server via the form and javascript.
0
 
asp_net2Author Commented:
Hi Randy,

What would you suggest in my scenario? Like I said before I'm collecting the DateTime in SQL Server using the GetDate() function and it's working great except for reporting purposes. My concern is that what would happen if GoDaddy decides to place my Server somewhere else in a different Time Zone instead of PST. So, if I continue to collect the DateTime like I'm doing now is there a way to modify the GetDate() function in SQL Server to also collect the TimeZone so that I can display that for reporting?
0
 
Miguel OzSoftware EngineerCommented:
You are better off saving them as UTC values using GETUTCDATE (Transact-SQL) method.

UTC values are independent of time-zones, you just need to transform these values to local time in your client browser. (e.g.. Using JavaScript). Thus all your date time will reflect proper local times to your clients but in the server all times are standardized to UTC so that you can  do whatever operations are required (e.g. comparisons)

Note: Even If you collect the date from the user, you still want to store as UTC value for the reasons stated above.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
asp_net2Author Commented:
@Randy Poole,

>> You can either continue to use the servers date/time then convert that value to the timezone for the client that way all timestamps are relative to a certain timezone.

So, if I continue to use the servers date/time then how can I convert that value to the timezone for the client? The users entering the form data will not see the date/time. It's only for reporting purposes, so when I retrieve there entry I want to be able to see what time that was completed based on the entries Timezone. If the entry was submitted in EST then it would be nice to see something like this as an example: 7-16-2014  07:21:33 AM  EST.
0
 
asp_net2Author Commented:
@Miguel,

>> You are better off saving them as UTC values using GETUTCDATE (Transact-SQL) method.

So would I just replace GETDATE() function in SQL Server with GETUTCDATE() ?

I have never used GETUTCDATE() before and I don't know anything about it.

Also, as I mentioned to Miguel, since the date/time will be for reporting purposes I think I may want to use the following example 7-16-2014  07:21:33 AM  EST. to show on the report in the event that I would get entries from different Timezones. Does that make sense or would I be better off storing as UTC and why?

Sorry for all the questions. It's sounds easy but then again I never created a form were I had to worry about possible entries dates from different time zones and I'm not sure how to handle that. I would just like to know the correct date/time that the end user submitted their form. If they are on EST then I would like to see the EST time for that entry. If they are PST then I would like to see their PST time and also show the timezone on the form for those entries.
0
 
Randy PooleCommented:
You would need to gather which time zone the user is in.  Even if you saved the timestamp from the client, you still would not know which timezone the TS is for.  16:25 will still be 16:25 no matter what.  You can get an idea of to the conversion using the following link:
http://msdn.microsoft.com/en-us/library/bb397769(v=vs.110).aspx
or you can just do a DateTime.AddHours with the timezone offset.
0
 
Miguel OzSoftware EngineerCommented:
Could you provide us with specific scenarios of how your user utilizes the date field and how are you planning to do the reports?
Note: Just suppose you have control on how to store the date info.  What are the expected results?

Hopefully this article will provide some insights about the issues with time zones and DLS.

Usage is a critical issue here. For example:
1) If the user data (date time) is the user birthday then we do not need to store it as UTC. or
2) If the user data is only used by the server then we only need UTC but not the time zones. The user will still see the conversion to local, but the user only sees this time as a reference.

There are more tricky conditions like user runs your site from different time zones, if that is the case every date time has to store not only UTC time, but also time zone info.
0
 
asp_net2Author Commented:
@Miguel,

>> Could you provide us with specific scenarios of how your user utilizes the date field and how are you planning to do the reports?

The user will not see the date/time that the form entry was created. It's only for reporting on my end.

When I view the reports this is the format that I see for the entry date/time:  7/17/2014 10:32:41 PM. So I have not idea what Time zone that entry was submitted. It would be nice to view the entry date/time as: 7/17/2014 10:32:41 PM EST. This way I know that the date/time was from the EST.

My database is located somewhere in PST since it's 3 hours behind. So all my entries are showing 3 hours behind. I would just like to see the correct date/time from the Time zone that the entry was submitted.
0
 
Miguel OzSoftware EngineerCommented:
Do you really need to know where the user date time comes from?
In other words, if you generate the time say in server time and generate your report in server time, then whether is  PST or EST is not important.
0
 
asp_net2Author Commented:
@Miguel,

>> Do you really need to know where the user date time comes from?

It would be nice to know the EXACT time of the entry. So, if I continue to have the database retrieve the date/time using the GETDATE() function then it's going to continually show me the date/time 3 hrs. behind, which is not a true recording.

This is what I'm searching for help on. I need to know the best METHOD for recording a users' date/time that a form entry was submitted. All of my entries have been submitted in EST but yet all my reports are showing me the date/time in PST. I just create a entry now and it showed me that I submitted the entry 3hrs ok.

So I would think that the best way to handle this would be to either have the database record the date/time with Time zone so that when I run the reports I see the true date/time with Time zone entries.
0
 
Miguel OzSoftware EngineerCommented:
OK, let me see I got it right: The requirement is to get the user local submitted time and the reporting in local server time.
We do not need to keep or display "user local submitted time"  to the user.

If that is the case , your solution could be:
At client, submit the date as UTC. You need to convert user local time (EST) to UTC using JavaScript.
At server, process user data and store date as UTC value received form user. Do not use SQL server functions
When reporting, convert stored UTC value to local server time (PST) and build your report accordingly.

Note: I use a similar approach for a background processing feature at work.
0
 
asp_net2Author Commented:
@Miguel,

The user will NOT see the Date/Time, it's only for reporting purposes.

I'm a little confused with what you are suggesting that I need. I'm trying to explain very clearly, perhaps I'm not making any sense so I will try again.

I would like to see the Date/Time along with the Time zone that the user submitted his/her application. So, If a user submitted his/her application on the West Coast then I want to see the EXACT time they submitted their form along with the Time zone. I would prefer this to be done on the Database automatically rather than using JavaScript if possible. Or I would prefer using ASP.NET C# code instead of JavaScript in the event they would happen to have JavaScript disabled.
0
 
Miguel OzSoftware EngineerCommented:
If that is the case then your site should:
Submit both the local time and time zone information and store it in your database. (Cannot be done on Database automatically)
At server, store date as local value/ time zone received from user. Do not use SQL server functions
When reporting, convert stored local value/ time zone to local server time/time zone (PST) and build your report accordingly, check the TimeZoneInfo class.

Notes:
1) JavaScript disabled is very rare these days because most sites use it by default.
2) What ASP.NET version are you using? MVC?
3) Check Converting Between Any Two Time Zones at http://msdn.microsoft.com/en-us/library/bb397769(v=vs.110).aspx
It explain how to use the TimeZoneInfo class.
0
 
asp_net2Author Commented:
@Miguel,

>> What ASP.NET version are you using?
I'm using ASP.NET 4.5 with C#

>> MVC?
I may plan on moving this application to MVC 5 in a few months

Also, I looked at that site just now and it doesn't make any sense to me at all :( Which is why I posted here if the event someone could help assist me with the code. So, if I need to collect the DateTime/Time zone info on code then I'm not sure how to capture that. I'm sure I need to capture that to a hidden field and then add to Database but just not sure what format to use to collect that info. Then I would need help showing that on report page.
0
 
Miguel OzSoftware EngineerCommented:
In javascript, you could use
    var current_date = new Date(); //convert it to string (yymmddHHmmss) for transmission
    var offset = -current_date.getTimezoneOffset() / 60; //number

and then you can transmit this info as part of your submit data. Just keep in mind that this code will fail for DLS. (The offset changes +/-  1 hour)

Note: Some sites recommend this detection library to deal with time zones and DLS issues. (I have not used my self though). It is the same concept but the offset is returned as a string.
0
 
asp_net2Author Commented:
@Miguel,

Is there a way to do this without JavaScript? Such as just collecting the users DateTime & Timezone using ASP.NET C# Systsem.Date.Time format?
0
 
Miguel OzSoftware EngineerCommented:
Not really, because the client and server do not share that info.
A possible alternative: The following link shows a solution that generates the local date (using JS) in C#.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 8
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now