"handle" and "change" columns in sqlite export

(This new post relates partly to this closed discussion but also raises some new points. Might be of interest to @StoltHD and @Connie and others.)

Having studied the previous discussion, the code, and the data in my own sqlite export, I have the following understanding. I hope more knowledgeable people will kindly correct me if I am mistaken.

The handles generated by Gramps are from 16 to 32 characters in length, where each character represents a hex digit. The first part (a minimum of 8 hex digits) is derived from a timestamp, and the second part (also a minimum of 8 hex digits) is derived from a random number.

Realistically, exactly the first 11 hex digits represent the timestamp, because timestamps for dates ranging from 1973-06-26 through 2025-09-30 result in an 11-digit hex value, and Gramps has only been in use between those two dates. Indeed, in my database the handles range in length from 19 to 27 characters (11 for the timestamp, plus 8 to 16 for the random number).

To create a new handle value in sqlite, following the pattern used by Gramps (and therefore hopefully insuring against a conflict with any existing handle value), I think this is what you need to use:

PRINTF ( ‘%08x’, CAST ( ( JULIANDAY (‘NOW’) - 2440587.5 ) * 86400.0 AS INTEGER ) * 10000) || PRINTF ( ‘%08x’, RANDOM() )

So I think it means that you could export your database to sqlite, add new rows using handles calculated as shown above (and of course taking care to ensure all of the correct linkages among the tables, which is not trivial), and then import your sqlite database into a new family tree in gramps.

As a side note, although the database already has a “change” column on many tables (representing the date of last change for the row; you would want to update that as well), the first 11 characters of the handle can be reverse engineered into a column representing the date of original insertion of the row.

For example, a row having a handle in which the first 11 characters are d897668328b must have been created on 2017-03-01:

DATE ( ( 0xd897668328b / 864000000.0 ) + 2440587.5 ) = 2017-03-01

But that trick will only work until 2025-09-30. After that date, newly created handles will have lengths of 20 to 28 characters instead of 19 to 27 characters. You would know that an (older) 19-character handle must consist of 11 characters for the timestamp, since the random number portion can’t be shorter than 8 characters. And you would know that a 28-character handle must consist of 12 characters for the timestamp, since the random number portion can’t be longer than 16 characters. But for handles of intermediate lengths you wouldn’t know whether the timestamp portion is 11 or 12 characters.

2 Likes

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.