CODEWITHSUNDEEP
databasesqlite
Streamline
Streamline

Reputation: 2122

Will sqlite column type affect performance for a long integer timestamp value?

In sqlite, is it more or less performant for a large database to have a column type of INTEGER or TEXT or DATETIME or TIMESTAMP when the values are going to be epoch timestamp in ms? i.e. 1384639972645 - they all seem to work, meaning I can define them as either of those and the value gets stored the same as far as I can tell.

Is there a difference in lookup performance if there are 100,000 or 1,000,000 records with this type of value in the column between column types?

Upvotes: 2

Views: 700

Answers (1)

CL.
CL.

Reputation: 180080

SQLite uses dynamic typing and does not care much about the declared column type; it does not matter whether you use INTEGER or TIMESTAMP or FLUFFY BUNNIES.

The only thing the column type is used for is to determine the type affinity. If you use type TEXT, all values will be stored in the database as strings, and must be converted from/to numbers when being used. All other types have NUMERIC affinity.

If you want to document what the data type is, use a type like TIMESTAMP_MS or MILLISECONDS.

Upvotes: 2

Related Questions