[briHass]

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]

Also valid. I just use ISO8601 and bite the bullet because storage is cheap.

[acuozzo]

FWIW, storage is cheap, but caches are not.

[di456]

It'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/

[acuozzo]

I 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]

I learned something new, thanks for sharing!

What format works well for hardware caching?

[acuozzo]

yw :-)

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]

> 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]

Are 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]

Agree, 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.