Date Last Edited – UTCModDate #date #datadefinitions

RM8 replaced Edit Date with UTCModDate

Previous versions had an EditDate column in a few tables. In #RM8, these have been replaced by UTCModDate which is present in most tables and appears to serve the same functions, one of which is to populate the “Date Edited” field in the People List view. The “Date Edited” field is derived from PersonTable.UTCModDate which is updated when a record related to that person in the EventTable or NameTable is added, deleted or changed.

Another change is that the value stored is no longer modified to ‘local time’ but is Universal Coordinated Time (UTC). In both cases, the value stored is the fractional number of days since noon of 30 December 1899.

Decoding UTCModDate

The following is an example query of the EventTable.UTCModDate:

SELECT UTCModDate,
DATE(UTCModDate + 2415018.5) AS Date,
TIME(UTCModDate + 2415018.5) AS Time,
DATETIME(UTCModDate + 2415018.5) AS DateTime
FROM EventTable
;

UTCModDate        Date        Time      DateTime
44578.8881889815  2022-01-17  21:18:59  2022-01-17 21:18:59
44565.8002626505  2022-01-04  19:12:22  2022-01-04 19:12:22

Encoding UTCModDate

SELECT julianday('now') - 2415018.5 AS UTCModDate;

RM7

Decode the Last Edited Date for Persons

Although the data definition for EditDate in the PersonTable has been divined for almost as long as this wiki has existed, it seems that no one has published a SQLite query that incorporates it. Here is one that provides the algorithm using SQLite expressions described at http://www.sqlite.org/lang_datefunc.html.

SELECT PersonID
    ,EditDate
    ,DATE (EditDate + Julianday('1899-12-30')) AS "Last Edited"
FROM PersonTable;

Decode the Last Edited DateTime for Events

The EventTable uses the same representation but at higher precision, incorporating time, the value to the right of the decimal. With slight modification, the SQLite query for the date and time of the EventTable EditDate becomes:

SELECT EventID
    ,EditDate
    ,DATETIME (EditDate + Julianday('1899-12-30')) AS "Last Edited"
FROM EventTable;

The NameTable also is set up with EditDate but all values are 0.0 as of RM 6.0.0.4.

The queries might benefit speed-wise by replacing Julianday(‘1899-12-30’) with 2415018.5.

Encoding System DateTime To Update EditDate

For updating or inserting records in these tables with timestamps from the operating system, the following queries provide the appropriate timevalues:

-- for PersonTable
SELECT JULIANDAY('now', 'localtime', 'start of day') - 2415018.5 AS EditDate;
 
-- for EventTable
SELECT JULIANDAY('now', 'localtime') - 2415018.5 AS EditDate;