New Posts New Posts RSS Feed: Recommended time format
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Recommended time format
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
scottarlp View Drop Down
Newbie
Newbie


Joined: 07-Apr-2011
Posts: 26 		Post Options Post Options   Quote scottarlp Quote  Post ReplyReply Direct Link To This Post Topic: Recommended time format
    Posted: 29-Aug-2011 at 11:00am
I've got a distributed app that is causing me trouble over time zones. I'm hoping someone can recommend what approach you should take to handle times using BOS. I realize this isn't necessarily specific to DevForce, but I can't seem to find anything elsewhere.
My architecture is SQL Server 2008R2, BOS and distributed WinForm/WPF apps. So the thick clients are running local to the user and therefore pick up their time zone information. I'm currently using DATE and SMALLDATETIME fields in SQL Server, so they are retrieved (stored) as local time relative to the server. Server is located in EST.
 
Here's what happens and some issues I'm having. Some to be expected - some I'm not sure of what the best practice is to handle.
 
If a user in CST enters a date to search on a DATE field in SQL Server, XML serialization picks it up and offsets it one hour so '2011-08-29' becomes '2011-08-29 01:00' when LINQ actually goes to query the database. SQL Server and LINQ in their wisdom decide it's ok for a query against a DATE field to include time and the query returns zero records. Is there a recommended way to handle this?
 
For date/time in general, should I be using the newer date offset data type in SQL Server to store date/time values? If a user enters a time in CST such as '2011-08-29 07:35', when it reaches the server, it becomes '2011-08-29 08:35' and then saves to the db as the last date/time. This is fine until one day our data center moves and is then located in CST. At that point, the db still shows the '08:35' time and XML serialization wouldn't change it back to '07:35'. None of the examples I see show using any other date/time data type, but could you offer your suggestion on how to handle this?
 
Lastly, all of this of course impacts reporting after the fact. So I'm trying to take all this into consideration on how I'm getting data out. We're currently using SSRS, which would run local to the server, so if the data doesn't stay relative to the time zone, it would appear to be off (a CST time converted to EST would look like it was off an hour).
 
What I would prefer to do (maybe not recommended) is keep everything relative to the time zone where data is entered. So if a CST entered time is '2011-08-29 07:35', that's how I'd like to store in the db because if someone in EST looks at a report for CST, they would want to see '07:35' (the time relative to CST in which something happened). This would also solve any reporting problems for our environment. Is there any way to get DevForce (or the XML serializer) to ignore the time zone offset?
 
Thanks for anyone's input and let me know if I can clarify further.
Scott
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 29-Aug-2011 at 12:46pm
Hi Scott,
 
Have you tried setting the DateTimeKind to Unspecified?
 
var date = myDate;
entity.property = new DateTime(date.Year, date.Month, date.Day, 0,0,0, DateTimeKind.Unspecified)
 
Regards,
   Silvio.
Back to Top
scottarlp View Drop Down
Newbie
Newbie


Joined: 07-Apr-2011
Posts: 26 		Post Options Post Options   Quote scottarlp Quote  Post ReplyReply Direct Link To This Post Posted: 29-Aug-2011 at 1:08pm
Yes and I've made the following observations.
 
- When I retrieve data and pass to the client, it has Kind = Unspecified. I'm guessing this is because the SMALLDATETIME field in SQL Server doesn't store time zone information, so somewhere in taking SQL data and creating the entity, it passes that information.
 
- When I add a new record client side, the date defaults to Kind = Local, which allows the problem to occur in the XML serialization. I am able to work around the problem if I intercept every single add/update before it goes to the SaveChanges() call.
 
Maybe I'm out of touch with things, but I see where data binding is being pushed more and more so I attempt to use binding sources and bind to grids and such automatically. Using this approach, when I go to save, I have to interject code that loops through all possible entities and set all the date's Kind value. And just to complicate future development, if I add a field to the database and it happens to be a date field, well, I have to remember to go add some custom code to handle setting Kind.
 
I've Googled and read most all of today and I'm just kind of shocked that either not that many people find this to be a problem or maybe just don't post about it. Even if I could go along with storing the time zone converted date in the database, has no one run into the problem where selecting a date from a calendar control gets time converted in the LINQ query and therefore querying a DATE field (no time here) results in a search for DATE+TIME (no results)?
 
Last time I had to cross time zones in a thick client (I have to support disconnected and other factors), I used .net remoting, which apparently doesn't have the same issues. Seems like all this progress foreward in technology just takes several more steps to complicating development and causing more overhead.
 
Enough babble on my part. :) So do you think the only way to preserve the local entered time is to intercept the data before SaveChanges() is called and set Kind? I haven't built a test SL app for this, but a disconnected SL app would have to run into the same issues (I guess a connected one would also since it executes locally).
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 29-Aug-2011 at 4:50pm
Scott,
 
I couldn't find any other approach. Howerver, rather than intercepting the data before saving, you could use the PropertyInterceptor:
 
[BeforeSet]
public void AfterSetAnyDateTimeProperty(IEntityPropertyInterceptorArgs args) {
  if (args.Value.GetType() == typeof(DateTime)) {
    var date = (DateTime)args.Value;
    args.Value = new DateTime(date.Year, date.Month, date.Day, date.Hour, date.Minute, date.Second, DateTimeKind.Unspecified);
  }
}
 
You can find detailed information about PropertyInterceptors in the DevForce Resource Center.
 
Regards,
   Silvio.


Edited by sbelini - 29-Aug-2011 at 8:10pm
Back to Top
scottarlp View Drop Down
Newbie
Newbie


Joined: 07-Apr-2011
Posts: 26 		Post Options Post Options   Quote scottarlp Quote  Post ReplyReply Direct Link To This Post Posted: 30-Aug-2011 at 3:49am
Thank you for that information. I think that will work better than what I thought I'd have to do. I had to write a Save Interceptor previously to allow empty strings to not be treated as null. But I'm stuck on the same thing as when I had to do that. How do I know how to model the class or what definition it needs. Could you add the class definition part outside the method you provided?
 
I created the following and put in my DM project, but it didn't have any impact. (Sorry, couldn't figure out how to paste pretty code). I'm assuming I need to inherit the class from somewhere, but I couldn't find anything on your referenced link that said how to actually implement it.
 
public class PropertyInterceptorsDefinitions
{
[BeforeSet]
public static void BeforeSetAnyDateTimeProperty(IEntityPropertyInterceptorArgs args)
{
if (args.Value.GetType() == typeof(DateTime) && !args.EntityProperty.Name.Equals("UpdateDate", StringComparison.OrdinalIgnoreCase))
{
var date = (DateTime)args.Value;
args.Value = DateTime.SpecifyKind(date, DateTimeKind.Unspecified);
}
}
}
 
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 30-Aug-2011 at 3:48pm
Hi Scott,
 
You can add the snippet above to the partial class of the entity you want to the logic to apply.
 
Or, if most of your entities would need such an interceptor, you could create a base class and add the interceptor to it. Beware that I wouldn't use this approach unless there is a considerably large number of properties that would need it (imagine this check being done for every property of every entity...)
 
Even in the snippet I provided, the logic will apply for every property of the entity in question. You could refine it to only trigger upon an specific property change:
 
[BeforeSet("MyPropertyName")]
 
By the way, you can automatically add partial classes and/or a base type by setting it in the edmx properties:
 
 
 
Regards,
   Silvio


Edited by sbelini - 30-Aug-2011 at 3:48pm
Back to Top
scottarlp View Drop Down
Newbie
Newbie


Joined: 07-Apr-2011
Posts: 26 		Post Options Post Options   Quote scottarlp Quote  Post ReplyReply Direct Link To This Post Posted: 31-Aug-2011 at 3:06am
Thank you for the information.
Scott
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down