How to extract unixtime from unique_rowid()

Hey All,

Is there a function to extract a unixtime from unique_rowid() ?

Thanks,

Cheers
Richard

I don’t believe there is. There are manipulations of the timestamp that occur when generating the ID and the unique_rowid() function only returns a 64 bit integer (really only 63 since the first bit is kept as 0 to keep them positive), so data is being lost as it’s converted.

I’d strongly recommend you look into adding a separate column to explicitly record timestamps (you can even use DEFAULT now() on the column type, so it’ll have similar behavior as currently, but without any risk of having the data munged and without needing to do any additional work.

… but for the sake of Science … :man_scientist:t3:

Here’s a piles of magic numbers and math that’ll get you the unix timestamp in nanoseconds if you really want it:

root@:26257/> SELECT ((unique_rowid() >> 15) * 1e4 + 14200704e11) AS unix_nanos;
+---------------------+
|     unix_nanos      |
+---------------------+
| 1511761641249810000 |
+---------------------+
(1 row)

However this only has 10-microsecond precision, and may decrease in accuracy if your cluster gets over 32k nodes, for… reasons. Also, it’s pretty wasteful to need to do the calculations all the time if you’re actually going to be using the data.

If you want to go a step further and get it as an actual TIMESTAMP type in cockroach, we can invite some division and casting to the party:

root@:26257/> SELECT (((unique_rowid() >> 15) * 1e4 + 14200704e11)/1e9)::INT::TIMESTAMP AS unix_timestamp;
+---------------------------+
|      unix_timestamp       |
+---------------------------+
| 2017-11-27 05:50:46+00:00 |
+---------------------------+
(1 row)

Due to the casting, this version only has second precision, sadly.

But seriously, if this is data you’ll need to access through a standard access path in your application, making a separate column for timestamps is way cleaner.

1 Like

For Science ! :slight_smile: I was secretly hoping I could just ’ >> n’, get a result in seconds and be all good. Alas … separate column it is.

Thanks Taylor,

Cheers
Richard