2-in-1 columns in SQLite tables

optimal-sqlite.php

<?php

/**
 * Returns current time in nanoseconds, which is a bigint (int64) and thus can be used as a primary key in SQLite.
 */
function get_sqlite_bigint_id_time_based(): int
{
    // The challenge here is that:
    // - microtime() returns current time accurate to the nearest microsecond, while we need it to nanosecond
    // - hrtime() returns a very precise time accurate to the nanosecond and is recommended for performance measurements,
    //   but it starts from an arbitrary point in time.
    //
    // So to create curren time that is accurate to the nearest nanosecond we combine the two.
    //
    // The biggest int64 value is 9223372036854775807.
    // When used as a unix timestamp in nanoseconds, it translates to Apr 12 2262,
    // which is in the far future more than in 200 years ahead.

    static $first_microtime_nanosec = null;
    static $first_hrtime_nanosec    = null;

    $hrtime = hrtime(as_number: true);

   if ($first_microtime_nanosec === null) {
        // The current time in seconds since the Unix epoch accurate to the nearest microsecond.
        $microtime = microtime(as_float: true);  // this is in SECONDS

        // Convert to nanoseconds bigint, but with the precision to the lowest microsecond, and three zeroes added.
        $first_microtime_nanosec = ((int) floor($microtime * 1000000))*1000;

        $first_hrtime_nanosec = $hrtime;
        $result = $first_microtime_nanosec;
    } else {
        // First comes "minus", then "plus" to avoid int64 overflow.
        $result = $first_microtime_nanosec - $first_hrtime_nanosec + $hrtime;
    }

    return $result;
}