0

We have an issue on a customer site, whereby we're importing a CSV file including two date fields (a start and finish date/time, with accuracy to seconds). The import code calculates the difference between the two dates as a TimeSpan, then we save the TotalSeconds to the database (in a real field).

Works perfectly in our development environment - but for some reason, on the customer site, the time difference is making some fractional error in the calculation, such that a time difference of 123 seconds frequently shows up in the DB as 123.0001 seconds, or 122.9999 seconds. We cannot reproduce the problem here.

I recall many years ago there was some issue with Pentium processors that they were making weird floating point calculation errors (such that they were nicknamed 5.0001-ium processors), but I don't recall the details. Is it possible that there might be a similar issue on the customer site, whereby date/time calculations are being messed up by a particular kind of processor? Can you think of any other possible reasons for this odd behavior?

The code is pretty simple. I've edited out some extraneous stuff, but it goes like this:

DateTime startDate, endDate; 
// set startdate and enddate by parsing from CSV file
var timeDiff = endDate.Subtract(startDate);
// and we save to the database using timeDiff.TotalSeconds
Shaul Behr
  • 36,951
  • 69
  • 249
  • 387
  • 6
    Why are you saving the value in a `real` field to start with, if you only want integers? It's hard to tell what's going on without seeing any code, too. – Jon Skeet Jun 17 '12 at 11:21
  • Is the calculation done in C# or in SQL server? – Joachim Isaksson Jun 17 '12 at 11:23
  • @JonSkeet - added code snippet, FWIW. It's in a `real` field because that's what the original programmer decided to do. I'm not going to try justify that decision; all I can say is that's what I've got to work with, and I don't have the option to change it to an `int`. – Shaul Behr Jun 17 '12 at 11:32
  • @JoachimIsaksson - C#. Code snippet added. – Shaul Behr Jun 17 '12 at 11:33
  • And sample values that are failing? Can you just round the value as you put it in the database? – Jon Skeet Jun 17 '12 at 11:41
  • @JonSkeet - There's nothing strange about the dates that are failing, like some singularity in Shanghai, 1927. (Yes, I saw and loved your answer http://stackoverflow.com/a/6841479/7850). The same date/times that are failing on the customer site are working fine in our dev environment. I could round the value on the way into the DB, but that wouldn't satisfy my intellectual curiosity... :-) – Shaul Behr Jun 17 '12 at 11:46
  • See [Wikipedia](http://en.wikipedia.org/wiki/Pentium_FDIV_bug) for more info on the bug. It would surprise me if the customer is running on such an old processor. – comecme Jun 17 '12 at 12:54
  • Do you know where the incorrect value occurs? In the C# code calculating the timediff, when storing the value in the database, or when retrieving the value from the database? Do you know what value actually is in the database? And can you trace what value is being sent from .NET to the database? – comecme Jun 17 '12 at 12:57

1 Answers1

0

Round the number to a whole number before you put it in the database.

Tim S.
  • 55,448
  • 7
  • 96
  • 122
  • Indeed, a very reasonable solution, though it will take a while to get that through QA and release procedures to get to the customer. I'm hoping to find something on the customer site that we can tweak for an immediate solution. – Shaul Behr Jun 17 '12 at 11:48
  • And now, having checked with other members of the team, we can't round the value before saving, because there are other usages of this column where we do need fractional values. It's only in the specific case of the CSV import that we expect integral values. Yes, we could manipulate the TimeSpan object in the CSV import code, but that's a little kludgy and doesn't really address the core issue. I'd prefer not to have to rely on that if there's a better solution that gets at the core of the problem. – Shaul Behr Jun 17 '12 at 12:14