Skip to content

SFS Timestamps

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 time

We use ceph::real_time as timestamps.

ceph::real_time is a uint64_t nanosecond count since epoch.

How to store time in SQLite

Requirements

Minimum microsecond resolution.

We can leverage SQLIte range queries, sorting, indices. Ideally we can leverage date / time functions with minor conversion.

Considered Options

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_time range. Conversion cost. Queries work.
  • uint64 - Squeeze uint64_t into SQLite INTEGER type representation. No queries.
  • blob - Store ceph::real_time as an SQLite blob. Can't query and index as easily as hex or int64 options.

Decision Outcome

We choose int64 ns, because it meets all criteria.

It requires minimal conversion. We can live with the year 2262 limitation.