From this documentation, the EditDates are defined as: “number of days from 12/31/1899; 1 = 1/1/1900, 36526 = 1/1/2000, etc”
This does not seem to be true. It is actually a fractional number of days from 1 Jan 1900 at midnight with day 1 being 2 Jan 1900. If that was intentional, the documentation is incorrect. If the documentation is correct, then these RM dates were implemented incorrectly.The justification for this conclusion appears below.
This date format appears to be a modified Julian date. The Julian day number for the day starting at 12:00 UT (noon) on January 1, 2000, 1s 2,451,545. The date used in RM appears to be a modified form of the Dublin Julian Date which starts on 31 Dec 1899 at noon. That is, the Dublin Julian Date uses Julian Date – 2415020 as a starting point. See Wikipedia under “Julian day” for details.
If the RM documentation is correct, the RM date should be Julian Date minus 2415020.5. The “0.5” represents a half day to adjust for starting at midnight instead of noon. However, using this assumption, the RM date appears to be off by two days.
PROOF: I added an event into my RM database on 19 Jun 2022 AT 10:02 pm CDT. Then I used SQLite to look up the EditDate. It was 44731.9182069097.
Due to the apparent relationship to Julian Dates we can use all of the Julian Date processors available to check the work. If we add 2415020.5 to the RM date value, we get 2459752.4182069097. Using Julian Date converters, this value represents 21 Jun 2022 at 22:02:12. The time is exactly correct. But the date is off by two days.
So it looks like the RM date starts at 1 Jan 1900 at midnight and day 1 is 2 Jan 1900.
CONCLUSION: The RM EditDates can be deciphered by adding 2415018.5 and using SQLite Julian date functions. ALSO. This field does not appear to be a “Date Last Edited”. It appears to be “Creation date”. Altering a fact and saving it again did not alter the EditDate field – at least in the EventTable.
In SQLite you can use strftime(“%d-%m-%Y %H:%M”, EditDate + 2415018.5) or datetime(EditDate + 2415018.5) to convert EditDate into more useful values.
The spreadsheet originated from observation of the earliest release of RM4. It’s possible the inferred description was accurate then and a change occurred in the app. Subsequent posts that have gone into the date formats in more detail and conversions to/from human-readable agree with your conclusion. See https://sqlitetoolsforrootsmagic.com/date-last-edited/
From this documentation, the EditDates are defined as: “number of days from 12/31/1899; 1 = 1/1/1900, 36526 = 1/1/2000, etc”
This does not seem to be true. It is actually a fractional number of days from 1 Jan 1900 at midnight with day 1 being 2 Jan 1900. If that was intentional, the documentation is incorrect. If the documentation is correct, then these RM dates were implemented incorrectly.The justification for this conclusion appears below.
This date format appears to be a modified Julian date. The Julian day number for the day starting at 12:00 UT (noon) on January 1, 2000, 1s 2,451,545. The date used in RM appears to be a modified form of the Dublin Julian Date which starts on 31 Dec 1899 at noon. That is, the Dublin Julian Date uses Julian Date – 2415020 as a starting point. See Wikipedia under “Julian day” for details.
If the RM documentation is correct, the RM date should be Julian Date minus 2415020.5. The “0.5” represents a half day to adjust for starting at midnight instead of noon. However, using this assumption, the RM date appears to be off by two days.
PROOF: I added an event into my RM database on 19 Jun 2022 AT 10:02 pm CDT. Then I used SQLite to look up the EditDate. It was 44731.9182069097.
Due to the apparent relationship to Julian Dates we can use all of the Julian Date processors available to check the work. If we add 2415020.5 to the RM date value, we get 2459752.4182069097. Using Julian Date converters, this value represents 21 Jun 2022 at 22:02:12. The time is exactly correct. But the date is off by two days.
So it looks like the RM date starts at 1 Jan 1900 at midnight and day 1 is 2 Jan 1900.
CONCLUSION: The RM EditDates can be deciphered by adding 2415018.5 and using SQLite Julian date functions. ALSO. This field does not appear to be a “Date Last Edited”. It appears to be “Creation date”. Altering a fact and saving it again did not alter the EditDate field – at least in the EventTable.
In SQLite you can use strftime(“%d-%m-%Y %H:%M”, EditDate + 2415018.5) or datetime(EditDate + 2415018.5) to convert EditDate into more useful values.
The spreadsheet originated from observation of the earliest release of RM4. It’s possible the inferred description was accurate then and a change occurred in the app. Subsequent posts that have gone into the date formats in more detail and conversions to/from human-readable agree with your conclusion. See https://sqlitetoolsforrootsmagic.com/date-last-edited/