|Title:||Wrong Datetime Sort Order When Using Fractional Seconds|
|Last Modified:||2017-07-11 19:44:38|
|Version Found In:||18.104.22.168|
anonymous added on 2017-06-14 19:05:51:
This is the exact same problem as stated in Ticket UUID 1098a1a85aea07336f4eeda03ee4ba793cd2c80e, and since I could not see how to reopen or append to that ticket I'm creating a new one. My Story: Windows 7 64-bit Visual Studio 2010 Express .Net 4.0 VB.Net Custom Application I'm storing records that come in batches and number in the thousands per second, each requiring a unique time stamp. This is accomplished by incrementing the tick count on the datetime format of the current time before storage via a bulk parameterized transaction every couple of seconds. For evaluation and other purposes, the data is then loaded and sorted by date. The problem is that the trailing zeros are removed when storing in the database, with an example sort being: 2017-03-04 13:39:47.8053927Z 2017-03-04 13:39:47.8053928Z 2017-03-04 13:39:47.8053929Z 2017-03-04 13:39:47.8053931Z 2017-03-04 13:39:47.8053932Z 2017-03-04 13:39:47.8053933Z 2017-03-04 13:39:47.8053934Z 2017-03-04 13:39:47.8053935Z 2017-03-04 13:39:47.8053936Z 2017-03-04 13:39:47.8053937Z 2017-03-04 13:39:47.8053938Z 2017-03-04 13:39:47.8053939Z 2017-03-04 13:39:47.805393Z 2017-03-04 13:39:47.8053941Z 2017-03-04 13:39:47.8053942Z 2017-03-04 13:39:47.8053943Z 2017-03-04 13:39:47.8053944Z 2017-03-04 13:39:47.8053945Z 2017-03-04 13:39:47.8053946Z 2017-03-04 13:39:47.8053947Z 2017-03-04 13:39:47.8053948Z 2017-03-04 13:39:47.8053949Z 2017-03-04 13:39:47.805394Z 2017-03-04 13:39:47.8053951Z 2017-03-04 13:39:47.8053952Z 2017-03-04 13:39:47.8053953Z 2017-03-04 13:39:47.8053954Z 2017-03-04 13:39:47.8053955Z 2017-03-04 13:39:47.8053956Z 2017-03-04 13:39:47.8053957Z 2017-03-04 13:39:47.8053958Z 2017-03-04 13:39:47.8053959Z 2017-03-04 13:39:47.805395Z 2017-03-04 13:39:47.8053961Z 2017-03-04 13:39:47.8053962Z 2017-03-04 13:39:47.8053963Z 2017-03-04 13:39:47.8053964Z 2017-03-04 13:39:47.8053965Z 2017-03-04 13:39:47.8053966Z 2017-03-04 13:39:47.8053967Z 2017-03-04 13:39:47.8053968Z 2017-03-04 13:39:47.8053969Z 2017-03-04 13:39:47.805396Z 2017-03-04 13:39:47.8053971Z 2017-03-04 13:39:47.8053972Z 2017-03-04 13:39:47.8053973Z 2017-03-04 13:39:47.8053974Z 2017-03-04 13:39:47.8053975Z 2017-03-04 13:39:47.8053976Z 2017-03-04 13:39:47.805397Z which should!!! be: 2017-03-04 13:39:47.8053927Z 2017-03-04 13:39:47.8053928Z 2017-03-04 13:39:47.8053929Z 2017-03-04 13:39:47.8053930Z 2017-03-04 13:39:47.8053931Z 2017-03-04 13:39:47.8053932Z 2017-03-04 13:39:47.8053933Z 2017-03-04 13:39:47.8053934Z 2017-03-04 13:39:47.8053935Z 2017-03-04 13:39:47.8053936Z 2017-03-04 13:39:47.8053937Z 2017-03-04 13:39:47.8053938Z 2017-03-04 13:39:47.8053939Z 2017-03-04 13:39:47.8053940Z 2017-03-04 13:39:47.8053941Z 2017-03-04 13:39:47.8053942Z 2017-03-04 13:39:47.8053943Z 2017-03-04 13:39:47.8053944Z 2017-03-04 13:39:47.8053945Z 2017-03-04 13:39:47.8053946Z 2017-03-04 13:39:47.8053947Z 2017-03-04 13:39:47.8053948Z 2017-03-04 13:39:47.8053949Z 2017-03-04 13:39:47.8053950Z 2017-03-04 13:39:47.8053951Z 2017-03-04 13:39:47.8053952Z 2017-03-04 13:39:47.8053953Z 2017-03-04 13:39:47.8053954Z 2017-03-04 13:39:47.8053955Z 2017-03-04 13:39:47.8053956Z 2017-03-04 13:39:47.8053957Z 2017-03-04 13:39:47.8053958Z 2017-03-04 13:39:47.8053959Z 2017-03-04 13:39:47.8053960Z 2017-03-04 13:39:47.8053961Z 2017-03-04 13:39:47.8053962Z 2017-03-04 13:39:47.8053963Z 2017-03-04 13:39:47.8053964Z 2017-03-04 13:39:47.8053965Z 2017-03-04 13:39:47.8053966Z 2017-03-04 13:39:47.8053967Z 2017-03-04 13:39:47.8053968Z 2017-03-04 13:39:47.8053969Z 2017-03-04 13:39:47.8053970Z 2017-03-04 13:39:47.8053971Z 2017-03-04 13:39:47.8053972Z 2017-03-04 13:39:47.8053973Z 2017-03-04 13:39:47.8053974Z 2017-03-04 13:39:47.8053975Z 2017-03-04 13:39:47.8053976Z The obvious fix is to keep the trailing zeros. The last ticket was closed as 'Works_As_Designed' with reasoning being that a fix would break backwards compatibility. However, I'd say breaking backward incorrect behavior would be a plus for the future as this bug has cost me several hours already in rectifying output from a 'correct routine' assuming the sort order was right. How would keeping seven digits after a decimal break anything? In my case as the date is a critical feature, continuing this bug is akin to the addition of 2.01 + 2.02 equaling 4.02...which is wrong. My request is to update either the datetime storage so that the current sort works correctly or customize the sort to return the correct order.
mistachkin added on 2017-06-15 15:22:13:
Have you tried using a custom DateTimeFormatString in the connection string?
mistachkin added on 2017-06-15 15:26:33:
For example, try adding the following to your connection strings: ";DateTimeFormatString=yyyy-MM-dd HH:mm:ss.fffffffK;"
anonymous added on 2017-06-27 14:59:32:
My fix was to create an extension when adding a parameter that explicitly saves the datetime as a string with the format: Time.ToString("yyyy-MM-dd HH:mm:ss.fffffffZ") Likewise the default System.Data.SQLite source could be modified to replace the incorrect datetime format for sorting ("yyyy-MM-ddTHH:mm:ss.FFFFFFFK") with the correct one ("yyyy-MM-ddTHH:mm:ss.fffffffK").
anonymous added on 2017-06-27 20:40:51:
I see you are still reticent to actually fix the problem for reasoning beyond my own. Are you afraid to recognize a bug? Is this pure laziness? Pride? I know this is open source, and I've also given of my time to bring this to your attention. Kindly reconsider your action as the solution is a simple one.
mistachkin added on 2017-06-27 22:55:40:
The behavior as you've described it is *NOT* a bug. The default format strings cannot be changed without breaking backwards compatibility. That being said, people are free to override them using the DateTimeFormatString connection string property, as I stated before. I'm not being lazy, nor prideful, and I do not appreciate your insinuations otherwise.
anonymous added on 2017-07-03 16:29:50:
Thank you for the clarification, but I disagree with you on the behavior not being a bug. There are two considerations that absolutely make this a bug. (1} SQLite purports to be a zero-configuration database that "just works". (2) The default settings for System.Data.SQLite output datetime strings that remove trailing zeros, which are then incorrectly ordered when sorted. In order for (1) to happen, (2) needs to be fixed so that the trailing zeros are not removed. This is an easy fix. The only backwards behavior I see changed is the retention of zeros if new data is written (not read). With new data, it will create a change in the sort order--the right order!!! That is why this constitutes a bug. Further, I'm not the first to report this behavior as a bug. In fact, it was an unpleasant surprise as already noted. Given the clear logic thus stated, you have yet to prove any breaking or incompatible change other than by saying there is one. I'm at a loss as to what I may have overlooked. Any enlightenment? A change in the code perhaps so that others needn't have this same problem?
mistachkin added on 2017-07-03 17:24:46:
Respectfully, I disagree with your assertions. Let me first address your points: > > (1} SQLite purports to be a zero-configuration database that "just works". > The SQLite core library is part of System.Data.SQLite; however, it does not have the concept of a DateTime data type. That is synthesized by System.Data.SQLite. > > (2) The default settings for System.Data.SQLite output datetime strings that > remove trailing zeros, which are then incorrectly ordered when sorted. > As far I as know, this has always been the case (at least since I started being responsible for maintaining the project. Changing the default format strings at this point would be far too disruptive to hundreds (or thousands?) of existing applications and libraries. There are almost certainly instances where the existing default DateTime format strings are relied upon exactly as they are. If the DateTime format string was changed to include leading zeros then people that are truncating the fractional portion of the DateTime and expecting it to fit within a certain number of characters would be broken. Also, you never mentioned whether or not you tried using the DateTimeFormatString connection string property. I assumed that it would fix your issue because it's designed exactly for use cases like this.
anonymous added on 2017-07-03 18:34:35:
Ahah! I think we are getting somewhere now. I'm not concerned with the leading but trailing zeros. The current functionality removes zeros from the end of the string leaving potential values such as: 2017-03-04 13:39:47.8053927Z 2017-03-04 13:39:47.805392Z 2017-03-04 13:39:47.80539Z 2017-03-04 13:39:47.8053Z 2017-03-04 13:39:47.805Z What needs to happen is to preserve the zeros in order to get the correct sort order (try sorting the above) as so: 2017-03-04 13:39:47.8050000Z 2017-03-04 13:39:47.8053000Z 2017-03-04 13:39:47.8053900Z 2017-03-04 13:39:47.8053920Z 2017-03-04 13:39:47.8053927Z This sorts the exact opposite of the first example. Programs relying on the current default behavior must expect dynamically changing character lengths. A static length would therefore fit within this model without breaking anything. The only change would be the sort order (and potentially increased storage space)--not the actual value. Note, this change will only apply to the fractional portion of the seconds and not affect other datetime formats omitting them. I'm sure quite a few programs will act differently with this change as the sort order will now be correct. In most cases, routines may actually work as intended since the incorrect behavior has been rectified. If someone wrote SQL to check and add zeros to the end, the worst case scenario would be they already existed. Again, programs relying on the default behavior must expect variable lengths. In short, there is no down side to this change. I've not used the connection string property for datetimes as at the time of my work around I changed my datetime outputs to be written directly as a string that I had formatted. Day one would have been the best time to implement the right behavior. But, now is the second best time. Since there are no backwards compatibility concerns as discussed, a change in default behavior would be appreciated. Other than this one instance, SQLite has been rock solid, so let's keep up with this reputation with reality.
mistachkin added on 2017-07-07 13:56:17:
Sorry, it was a typo, I meant trailing zeros.
anonymous added on 2017-07-10 22:01:00:
So, were you able to come up with an actual backwards compatibility scenario or does the logic hold?
mistachkin added on 2017-07-11 19:44:38:
The default DateTime format strings will not be changed. It's too risky, especially considering how easy it is to override them via the connection string.