DateTime Precision

[rpaul]

We are trying to update from version 4.5.192 to 5.3.521 and it appears that the datetime column type has changed from a fractional precision of 7 to 6. When we upgraded to 4.5.192 it had changed from a lower precision to 7.

Why is this changing every release? Why was it lowered from 7 to 6? I see in the release notes that it said increased precision to 6, which is a lie because it was at 7. These changes break our application as it starts trying to do inserts where updates are required and ends up with concurrency errors.

[Pinturiccio]

Why is this changing every release?

The precision of datetime type is changed only in dotConnect for SQLite 4.5.192 and dotConnect for SQLite 4.5.202. The precision was changed from 4 to 7 in 4.5.192 version, but it was not announced in the release notes because the change of precision was not completed in this version. The precision of datetime was not changed for the SQLiteDateTime type.

Then, we changed the precision of datetime for the SQLiteDateTime type as well. At that moment, we decided to change the precision to 6, what was done in the next version 4.5.202.
For more information, please refer to https://www.devart.com/dotconnect/sqlit ... story.html

I see in the release notes that it said increased precision to 6, which is a lie because it was at 7.

We have not announced that we would increase the precision to 7. In dotConnect for SQLite 4.4.179, the precision was 4. We have increased it to 6 in version 4.5.202.

If you need to work with datetime with the precision that equals 7, you can use the mode in which the datetime type is strored as integer. For this, in SQLiteConnectionStringBuilder, set up the SQLiteDateFormats.Ticks value for the DateTimeFormat property. For more information, please refer to https://www.devart.com/dotconnect/sqlit ... ormat.html

These changes break our application as it starts trying to do inserts where updates are required and ends up with concurrency errors.

If using dotConnect for SQLite 5.3.521 is breaking your application because this version has the precision 6 of datetime type, there are two wayouts:
1. return to using dotConnect for SQLite 4.5.192;
2. Redesign your app and/or database for working with the datetime types, the precision of which equals 6. If you encounter any difficulties with redesigning the database, send us the code that works with datetime and causes the issue. We would appreciate if you send us a test project with some small database which reproduces the issue.

[rpaul]

The last time we upgraded we ran into a similar issue and were told it was changing to 7, and to use 4.5.192:

http://forums.devart.com/viewtopic.php?f=29&t=25932

Yes, at our users' requests, we increased precision of fractional seconds during passing DateTime to the database from 4 to 7 digits after the delimiter.

But now you say:

We have not announced that we would increase the precision to 7. In dotConnect for SQLite 4.4.179, the precision was 4. We have increased it to 6 in version 4.5.202.

So you released and told us to use a version that was a work in progress? This is not a matter of requiring 7 for our application to function, we already use ticks for fields that are sensitive to fractions of seconds. However you break any query on a DateTime column when you change the precision like that.
Saving '2001-01-01' to a database and then querying for '2000-01-01 00:00:00.0000000' no longer returns results as it has now been persisted as '2000-01-01 00:00:00.000000'.

Is this going to change back to 7 or is 6 the intended precision persisting to the database?

[Pinturiccio]

By the link specified http://forums.devart.com/viewtopic.php?f=29&t=25932, there was an issue we fixed. In result of the fix, precision has been changed from 4 to 7. But, the SQLiteDateTime type has not been changed.

That user pointed out that precision has been changed, so that we informed them the following:

Yes, at our users' requests, we increased precision of fractional seconds during passing DateTime to the database from 4 to 7 digits after the delimiter.

This information has not been added to the release notes and was provided to the user, since they faced the precision change, about what they wrote in the same forum thread.


After the passage cited above, we informed, that starting from the next public build, the precision will be changed to 6, not to 7 and it will be the same for both, DateTime and SQLiteDateTime types.

Is this going to change back to 7 or is 6 the intended precision persisting to the database?

We won't do such changes, because of the following reasons:

1. Since precision 6 is still used in SQLiteDateTime type, it may lead to confusions. If the SQLiteDateTime and DateTime types are used simultaneously, precision will be different.

2. Presicion 7 for the DateTime was used only in version 4.5.192. Precision 6 is used in dotConnect for SQLite for about three years, and changing it to 7 will break the backward compatibility for many users.