4 ms·
You really don't want to store timestamps as text. It's horrible for performance. A iso8601 timestamp takes at least double the amount of bytes (without a time
by the_duke 5y ago
You really don't want to store timestamps as text.
It's horrible for performance. A iso8601 timestamp takes at least double the amount of bytes (without a timezone), but more importantly you need to parse and validate the timestamp each time you use it. Filtering and sorting suffer a lot. Iso8601 without timezones can in theory be sorted trivially without parsing, but sorting strings is still a lot more expensive than sorting arrays of int64. And you also have to make sure to never write a different timezone. Why not just use an int?
You then have to parse the string into the native datetime type again in application code.
You also need to add a custom check constraint to prevent writing garbage to the column, which is easy to forget.
- littlecranky67 5y agoSQLite is not postgres. Often sqlite dbs span only a couple of MB, ie when used in an App, and performance might not be as important as readability during debugging/development.
- hnarn 5y agoThe argument "this doesn't matter because all SQLite databases are small anyway" does not hold water. There are many large SQLite databases out there. And even if there wasn't, storing timestamps as text is simply and fundamentally A Bad Idea(tm).
- littlecranky67 5y agoTrue, but I think SQLite has a design goal of being an embedded DB. As for your argument to store timestamps as text, look at DynamoDB who only allows storing dates as text, and it is designed to handle petabytes of data.