System.Data.SQLite

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.

Have you tried using a custom DateTimeFormatString in the connection string?

For example, try adding the following to your connection strings:

    ";DateTimeFormatString=yyyy-MM-dd HH:mm:ss.fffffffK;"

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").

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.

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.

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?

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.

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.

Sorry, it was a typo, I meant trailing zeros.

So, were you able to come up with an actual backwards compatibility scenario or does the logic hold?

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.