The ongoing saga of DateTime weirdness?

Question

A variety of posts hint at hassles:

http://forums.arcgis.com/threads/32404-DateTime-wrong-after-FeatureService-Editing?highlight=datetime+offset
http://forums.arcgis.com/threads/16644-Date-Conversion-Problems-REST-lt-gt-Silverlight
http://forums.arcgis.com/threads/20426-Did-the-date-format-change-for-Query-REST-Post-JSON-responses-at-ags-10

As I understand those other posts, the ESRI Silverlight API/REST API converts DateTimes (if Kind is Undefined or Local) to UTC before sending them to the database to be saved. And DateTimes received from the database will have Kind=Utc. That is, I think this means these APIs assume my database is storing UTC time.

In my case, the database is understood by our enterprise and its other information systems to hold local times.

I'm not using any ESRI Silverlight API UI to display dates, like FeatureDataForm or FeatureDataGrid - only Feature tips and an Identify tool.

In Silverlight, when editing a feature, I create a DateTime with DateTime.Now, or DateTime.Now.ToLocalTime(). I assign that DateTime to a Date field on the current edit graphic. Viewing the graphic with an Identify tool shows the time to be correct, local. And then I save the edit.

The geodatabase is Oracle, and that Date field is the Date datatype. Using a database viewer such as Tora or Toad, I see that the time as committed to the database is 6 hours in the future. We are in the Central time zone, UTC-6.

Now after the save, using an Identify tool on the edited feature shows the same time - from the database - 6 hours in the future.

If our enterprise understood the DateTimes in our tables to be UTC, I suppose I could do what those other posts suggest and modify any code in my app (feature tips, Identify tool in my case) that displays DateTime values to reset the Kind property of DateTimes received from the database back to Local with a DateTime.ToLocalTime() call, so they get displayed as local time.

But other information systems use the same Oracle tables, and THEY take (and edit) the times in the database at face value - local time. So what is saved to the database has to be local time.

So far I've handled this by subtracting 6 hours from DateTime.Now before I assign it to the graphic to be saved, so that when the API changes it to UTC to save, adding 6 hours, the time as stored in the database is local time. As a consequence, the graphic as displayed in the app appears to be 6 hours in the past, until it is refreshed with a new database query.

Has anyone any better suggestions on this topic?

Sorry if I got longwinded, I hope you are still awake...

Thank you, MC

markcheyne · Answer

Pardon me while I include you all in my inner monologue.

I implemented a solution that does exactly what my last update asked about - I just had to remember that one can set his own Converter (IValueConverter) on a Binding object.

Here is where we set up binding on the popup window. My change is in bold.foreach (string field in fields)
{
 if (aliases.Keys.Contains(field))
 {
  TextBlock valueBlock = new TextBlock() { TextWrapping = TextWrapping.NoWrap };
  Binding valueBinding = new Binding() { Path = new PropertyPath(string.Format("[{0}]", field)), StringFormat = aliases[field] + ": {0}" };
  valueBinding.Converter = new DateTimeConverter();
  valueBlock.SetBinding(TextBlock.TextProperty, valueBinding);
  stackBox.Children.Add(valueBlock);
 }
}

And here is the implementation of the DateTimeConverter:public class DateTimeConverter : IValueConverter
{
 public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
 {
  if (value is DateTime)
   return (((DateTime)value).Kind != DateTimeKind.Utc) ? ((DateTime)value).ToUniversalTime() : value;
  else
   return value;
 }

public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
 {
  return value;
 }
}

So, to recap, when I edit a graphic and set its date to a DateTime.Now, the resulting DateTime is of Kind=Local. I subtract 6 hours from it, so when it is saved, in the process being converted to UTC by the API, what ends up in Oracle is the correct local time. But having subtracted 6 hours, my tip window shows the date as 6 hours in the past - now corrected by this Converter. Any DateTime coming out of the DataBase is marked with Kind=Utc by the API, so it is ignored by this Converter.

Whew!
So please continue with your regular scheduled program, thanks for listening!

markcheyne · Answer

Update - I've decided that if I could modify the code in my popup window that I assign to FeatureLayer.MapTip to tweak the DateTimes as they are displayed, I'll be golden.

But it might exceed my kung-fu. I'm using simple data binding to the graphic's Attributes dictionary. But what I want to do is:

"if the value is a DateTime, and its Kind != Utc, add 6 hours to the value, THEN display it." Not like any PropertyPath my feeble mind can conjure...

I know I'm stretching, but if you have any thoughts, I'm grateful!