What are some ways to address this issue?
In order to store less precision, you can try the following methods:
- Attempt to configure your database platform to only store milliseconds and disregard any extra precision (which may be challenging with SQLite)
- Ensure that you insert values with the desired precision every time (although it might be difficult to cover all scenarios)
Is there a Javascript datetime object that allows for microsecond accuracy?
If you encode your dates as Strings or Numbers, you have the ability to include as much accuracy as needed. There are alternative options available (some of which are discussed in this thread). However, if extreme accuracy is not necessary, pursuing this approach may not be the most practical.
Can times in the database be truncated to milliseconds?
Yes, but since you are using SQLite, the process can be somewhat unconventional. SQLite does not truly have date types; rather, values are stored in fields designated as either text, real, or integer. The underlying storage classes determine the precision and range of the values that can be stored. Additional information on these differences can be found here.
For example, switching the underlying storage class to integer could result in truncating dates stored within that field to a precision of 1 second. When executing queries from JavaScript, you could also truncate your dates utilizing the Date.prototype.setMilliseconds() function. For instance..
MyModel.objects.filter(time__lte = javascript_datetime.setMilliseconds(0))
A more advanced DB platform would provide better handling in this scenario. In PostgreSQL, for instance, you can specify the exact precision at which timestamps are stored, down to milliseconds (matching that of JavaScript)..
alter table "my_table" add "my_timestamp" timestamp (3) with time zone
MySQL also offers similar capabilities, allowing you to specify precision.
.. or request reduced accuracy in queries?
Although possible, this approach is typically not advised.
If the filtering criteria being used is too precise, then truncation can be applied before filtering (as shown in the ..setMilliseconds() example above). However, if the values stored in the database that are being compared against are overly precise, complications may arise.
You might attempt to format or truncate the stored values to decrease their precision before comparison with your criteria, yet this operation must be performed for each value stored. Considering this could involve millions of values, any indexes created for the stored values may become ineffective due to dynamically generated values.