System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 1098a1a85aea07336f4eeda03ee4ba793cd2c80e
Title: Entity Framework: DateTime format is not sortable
Status: Closed Type: Incident
Severity: Important Priority: Medium
Subsystem: Convert Resolution: Works_As_Designed
Last Modified: 2015-12-05 01:02:29
Version Found In: 1.0.98.1
User Comments:
anonymous added on 2015-09-17 09:22:57:
The Entity Framework mapping stores DateTime values in a column of type datetime in the following format: yyyy-mm-dd hh:mm:ss.fffffff'Z'. The final Z indicates UTC time which I have set for my time values. The problem is that if there are trailing zeros in the sub-second part, they are dropped. This can lead to the following stored time values:

2015-09-17 09:19:20.123456Z
2015-09-17 09:19:20.1234567Z

It is clear that the first line is earlier than the second, but when sorting, the following is the result for a string comparison:

2015-09-17 09:19:20.1234567Z
2015-09-17 09:19:20.123456Z

This is the wrong sort order! When using this format, time values cannot be reliably sorted!

Instead, all trailing zeros must be stored as well to keep the datetime value at a constant length.

Mixing Z and non-Z values will of course also cause trouble but this is under the control of the developer who sets the data to write.

mistachkin added on 2015-09-17 18:08:45:
I'm not sure I understand the issue here.  Where is the DateTime format string
in question coming from?

mistachkin added on 2015-09-18 00:50:49:
If it's coming from the System.Data.SQLite assembly itself, those legacy
format string cannot be changed without breaking backward compatibility.

That being said, the DateTimeFormat and DateTimeFormatString connection
string properties can be used to override the DateTime format handling
on a per-connection basis.