Obi-Wan PIM

In org-roam v2.2.2, the only stored dates are the access time and modification times of the files respectively called atime and mtime. At the first time, I didn't understand the format being a list of four integers. The way to decode it is explained in the elisp documentation of the function decode-time. So in SQL, we can decode these times using the following SQL query.

select atime,
       datetime(substring(atime, 2, 5)*power(2,16) + substring(atime, 8, 5), 'unixepoch', 'localtime') as atime,
       mtime,
       datetime(substring(mtime, 2, 5)*power(2,16) + substring(mtime, 8, 5), 'unixepoch', 'localtime') as mtime
from files  order by mtime desc limit 5;

I was quite happy to be able to decode these times, but I realized that they are not very informative. The access time is not very useful and do not seems to be kept up to date. While the modification of the file is not always related to the modification of the note it contains, especially in my case, since my notes are synchronized between several computers and have been restored from backup. At the end, most of the modification times stored in SQLite do not correspond to the last time I have modified the file. To better keep trace of the modification time and have access to the creation date, I decided to install org-roam-timestamps, which stored the times in the properties of each node. Therefore, the following SQL query returns what I want:

select id,
substring(properties, instr(properties, 'CTIME') + 10, 14) as ctime,
substring(properties, instr(properties, 'MTIME') + 10, 14) as mtime
from nodes  limit 5;