Skip to content

Instantly share code, notes, and snippets.

@meglio
Created April 15, 2024 01:14
Show Gist options
  • Save meglio/3d6c91e0198431c216b58d5873cb4b94 to your computer and use it in GitHub Desktop.
Save meglio/3d6c91e0198431c216b58d5873cb4b94 to your computer and use it in GitHub Desktop.
2-in-1 columns in SQLite tables
<?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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment