Context and Problem Statement
We need to create, handle and persist timestamps.
In the RGW space we have ceph_time.h and std::chrono.
SQLite represents time as ISO-8601, Julian day or unix timestamps. Stored in TEXT, REAL or INTEGER data types. SQLite doc: data types. It has functions to work with these data types. SQLite doc: datefunc.
This doc is about the conversion between RGW/sfs and SQLite space.
Summary of discussion in weeks 19, 20 2023. GH Comments
ceph::real_time as timestamps.
ceph::real_time is a
uint64_t nanosecond count since epoch.
How to store time in SQLite
Minimum microsecond resolution.
We can leverage SQLIte range queries, sorting, indices. Ideally we can leverage date / time functions with minor conversion.
Options, that don't fit our requirements:
- ISO8601 strings. Not enough resolution.
UNIX time - Not enough resolution
multicol - Store seconds as ISO8601 or UNIX time (SQLite functions work directly). Store nanoseconds in a second column. Queries awkward.
- int64 ns - Store as int64, nanosecond resolution, convert from uint64. Max value up to year 2262. Queries work.
- int64 us - Store as int64, microsecond resolution, convert from uint64. Max value up to year 2554. Queries work.
- hex - Store as 16 char hex string. Full
ceph::real_timerange. Conversion cost. Queries work.
- uint64 - Squeeze
INTEGERtype representation. No queries.
- blob - Store
ceph::real_timeas an SQLite blob. Can't query and index as easily as hex or int64 options.
We choose int64 ns, because it meets all criteria.
It requires minimal conversion. We can live with the year 2262 limitation.