Datetime String To Number Of Milliseconds

I pull data from a websource and it returns time in the format 'YYYY-MM-DDTHH:MM:SS.SSSZ'. Since I would be doing a lot of comparisons on this column, I thought it would be best to

Solution 1:

I'm guessing it has something to do with SQLite not having actual Date or Datetime types (section 1.2). Instead it formats them as one of:

• an ISO8601 string (TEXT type)
• a floating point number of days (REAL type)
• an integer number of seconds since the epoch time (INTEGER type)

The REAL data type may not have enough precision to store a date, time, and fractional seconds in a single field.

It seems the answer is one of:

• store them as two separate fields, the datetime in one, and the fractional seconds in another
• do the integer number of milliseconds calculation yourself and store the integer result in a single column, so long as the maximum level of fractional second precision you need will fit in a signed integer of 8-bytes or fewer (the maximum size for the INTEGER type in SQLite3)
• switch databases to a DBMS that supports real date/datetime types

Without switching your DB, and if you never need sub-millisecond precision, then I'd at least try the "manual calculation + single integer column" solution and see if it works.