Add a datetime with a specific string format to a SQLite database

Question

I have a C# application that is adding row to a SQLite database table. The timestamp is a DateTime object with the milliseconds set to a value and the Kind is set to Utc.

When I add many of these to the database, I'll have rows with the same time except the milliseconds are different. Some milliseconds have the trailing 0 cut off, but still have the 'Z' to indicate UTC timezone.

The problem I'm having is that when I get the rows and order by the timestamp, the rows with the non-zero milliseconds will appear before the rows with truncated-zero times.

I think this is because the "order by" is comparing a number to a 'Z'.

Here are 2 rows and the order they are returned:

2019-07-26 20:02:38.41Z
2019-07-26 20:02:38.4Z

I want the "38.4" time to come ahead of "38.41".

Question How do I store the DateTime object in the database with non-truncated 0 milliseconds?

or

What SQL statement do I use to order the datetime column correctly?

Perhaps this answer is the one to look for: https://stackoverflow.com/questions/17227110/how-do-datetime-values-work-in-sqlite — Steve, Jul 26 '19 at 20:17
I suggest store the number of ticks instead of string. That way the ordering is natural and is easy to compare dates. The downside is that it isn't human readable anymore. — Magnetron, Jul 26 '19 at 20:27
Manually storing as text and manually formatting that text is the problem. The SQLIte provider is equip with a large internal converter which makes it capable of managing [all sorts of data types including DateTime (and also in UTC)](https://stackoverflow.com/a/44312936/1070452). — Ňɏssa Pøngjǣrdenlarp, Jul 26 '19 at 22:07

score 3 · Accepted Answer · answered Jul 26 '19 at 20:57

Try to use strftime function. See Date And Time Functions.

select strftime('%Y-%m-%d %H:%M:%S.%f', '2019-07-26 20:02:38.41Z')
select strftime('%Y-%m-%d %H:%M:%S.%f', '2019-07-26 20:02:38.4Z')

It returns

2019-07-26 20:02:38.38.410
2019-07-26 20:02:38.38.400

These values can be sorted correctly.

For example

select *
from table
order by strftime('%Y-%m-%d %H:%M:%S.%f', datetimecolumn);

lewa9 · Answer 2 · 2019-07-26T20:37:16.140

1

Like other persons already mentioned SQLLite doesn't have a real date type. You can test this thing to avoid that the 'Z' char appears in your ORDER BY statement if you remove it.

SELECT date
FROM test
ORDER BY REPLACE(date,'Z', '');

edited Jul 26 '19 at 20:37

answered Jul 26 '19 at 20:16

lewa9

41
6

score 1 · Answer 3 · answered Jul 26 '19 at 20:40

I would suggest storing the dates as ticks, instead of strings, as Magnetron suggested, but if you want to keep the string format, you could manually format your dates into string with something like this:

var date = DateTime.UtcNow;
string dateString = date.ToString("yyyy-MM-dd hh:mm:ss.fffZ", CultureInfo.InvariantCulture);

Add a datetime with a specific string format to a SQLite database

3 Answers3