CODEWITHSUNDEEP
datetimesqlite
x-treme
x-treme

Reputation: 1636

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 store them as the number of millliseconds since epoch (I need millisecond precision!!). What would be the best way to convert the incoming datetime string to number of milliseconds?

I tried to use

strftime('%s', datetimeString) - gives back the number of seconds since epoch till datetimeString

strftime('%f', datetimeString) - gives back only the SS.SSS part!!

Upvotes: 1

Views: 1745

Answers (1)

jefflunt
jefflunt

Reputation: 33954

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.

Upvotes: 2

Related Questions