4 ms·
The biggest one missing is date and/or time. The workarounds all suck: - Store the date as a huge, wasteful string in ISO8601 format - Store it as Unix epoch
by briHass 4y ago
The biggest one missing is date and/or time. The workarounds all suck:
- Store the date as a huge, wasteful string in ISO8601 format
- Store it as Unix epoch seconds
- Store it as a fractional Julian day
Besides the first one, you have to remember how the date is stored and ensure all client libraries handle the conversion. If you want to view or manipulate the latter 2 formats in SQL, you need to chain a bunch of conversion functions.
- deathclassic 4y agoAlso valid. I just use ISO8601 and bite the bullet because storage is cheap.
- acuozzo 4y agoFWIW, storage is cheap, but caches are not.
- di456 4y agoIt's cheap if the cache is also on SQLite. Might not even need a separate db for some use cases. This runs on python and works with several backends including SQLite. https://requests-cache.readthedocs.io/en/stable/ https://requests-cache.readthedocs.io/en/stable/
- acuozzo 4y agoI meant hardware caches like L1, L2, and L3 on the CPU. SQLite is used in some HPC work. ISO-8601 datetime strings can easily wreck your L1 cache. Instead of filling an eighth of the cache with a 64-bit value, you wind up filling almost half with a 27-character-long string.
- di456 4y agoI learned something new, thanks for sharing! What format works well for hardware caching?
- acuozzo 4y agoyw :-) As small as you can tolerate tbh. The game is played by keeping data as small as possible and ensuring that related data has good locality in memory. The gap between how fast your CPU works and how fast data can be retrieved from RAM is enormous.
- bob1029 4y ago> Store it as Unix epoch seconds This is what we do. Have been storing 100% of our timestamps this way in SQLite for ~8 years now. Using .NET to handle the actual conversion to/from long. var myTimeUtc = DateTime.UtcNow; var myTimeUnix = new DateTimeOffset(myTimeUtc).ToUnixTimeSeconds(); var myTimeUtc2 = DateTimeOffset.FromUnixTimeSeconds(myTimeUnix).UtcDateTime; No drama at all. No weird libraries or utility methods. It's all simple built-ins these days.
- torbica 4y agoAre you maybe hiring? I have been reading you posts last couple years on HN. I am doing something very similar with .NET stack (single file deployments), SQLite and offline scenario... Please contact me on my username's email (gmail).
- srcreigh 4y agoAgree, this is a giant PITA. I think this could be easily fixed. The main sticking point with unix epoch is the SQLite CLI interface, but there could easily (?) be added some kind of mark to columns that it's an epoch timestamp and a client feature which parses those and formats the date. Done, problem solved. Any code (usually one codebase) which looks at dates in a SQLite DB can already easily do these conversions, even at the application-level.