Contents
Storage formats
Within the RootsMagic 4 database, several date-related fields exist. These fields can be grouped into into four different storage types:
FLOAT, with the integer part representing number of days since 1899 Dec 31 and fractional part representing time of day
EventTable – EditDate
LinkTable – extDate (presumably)
NameTable – EditDate (presumably)
PersonTable – EditDate (actually effectively INTEGER stored as FLOAT, meaning representing number of days since 1899 Dec 31)
INTEGER, 64-bit position-coded starting 10000BC
EventTable – SortDate see Dates – SortDate Algorithm
MediaLinkTable – SortDate
NameTable – SortDate
ResearchTable – SortDate1
ResearchTable – SortDate2
ResearchTable – SortDate3
INTEGER, representing calendar year (yyyy)
NameTable – BirthYear
NameTable – DeathYear
TEXT, represented by format explained in the Date sheet within RootsMagic4DataDefs.ods
EventTable – Date
MediaLinkTable – Date
NameTable – Date
ResearchTable – Date1
ResearchTable – Date2
ResearchTable – Date3
Discussions & comments from Wikispaces site
What triggers EditDate in NameTable?
23 January 2010 14:55:51
All mine are 0, even after editing a name.
romermb
23 January 2010 17:48:20
Tom, my notes for that table seem to indicate that I didn’t think that field was yet being used. Perhaps it’s intended for something going forward.
My other thought was that it might’ve originally been intended for use with Alternate Name facts only (as EditDate is used for other events in EventTable), but somehow was overlooked.
ve3meo
Decoding EventTable EditDate
23 January 2010 19:24:07
This appears to work, at least for EST. There might have to be other fractional fiddles for other time zones and DST.
SELECT EditDate, DATE(substr(EditDate,1,5)+2415018.5) AS Date, time(+substr(EditDate,6)-0.5) AS Time, datetime(EditDate + 2415018.5) AS 'Date/Time' FROM eventtable ;
romermb
23 January 2010 19:50:18
When playing around with the math last night, I’d come up with:
SELECT EditDate, DATE(EventTable.EditDate + 2415018.5), TIME(EventTable.EditDate + 2415018.5), DATETIME(EventTable.EditDate + 2415018.5) FROM EventTable ;
The 2415018.5 value is the number of days from time 0 (1 Jan 4713BC 12:00PM) in the Julian calendar to just before the EditDate value picks up.
thejerrybryan
MS Access and SQLite Functions for base 1899 dates
01 July 2011 15:24:09
MS Access has built-in functions that will handle the “base 30 Dec 1899” numeric dates such as PersonTable.EditDate. Namely, Year(PersonTable.EditDate), Month(PersonTable.EditDate), and Day(PersonTable.EditDate) “just work”. All the functions return a numeric value with the Year being the year, Month being 1 through 12, and Day being 1 through 31.
I can’t find equivalent functions on the SQLite side of the house for dealing with “base 30 Dec 1899” numeric dates. Does anybody have code for these dates that will work with SQLite?
Thanks,
Jerry
ve3meo
02 July 2011 04:01:33
From a prior discussion on this page, this might be helpful:
SELECT DateTime(2415018.5 + UTCModDate),
strftime('%m', 2415018.5 + UTCModDate) as MonthNum,
substr('UnkJanFebMarAprMayJunJulAugSepOctNovDec', 3*strftime('%m', 2415018.5 + UTCModDate)+1,3) AS Month,
CASE strftime('%m', 2415018.5 + UTCModDate)
WHEN '01' THEN 'Jan'
WHEN '02' THEN 'Feb'
WHEN '03' THEN 'Mar'
WHEN '04' THEN 'Apr'
WHEN '05' THEN 'May'
WHEN '06' THEN 'Jun'
WHEN '07' THEN 'Jul'
WHEN '08' THEN 'Aug'
WHEN '09' THEN 'Sep'
WHEN '10' THEN 'Oct'
WHEN '11' THEN 'Nov'
WHEN '12' THEN 'Dec'
ELSE 'Unk'
END
AS MonthText
FROM PersonTable;
SQLite Date & Time functions are described at https://www.sqlite.org/lang_datefunc.html
Tom
Well I have finally crossed over to SQL and yes its is a challenge. Sixty is probably not the age to start however I have had a great few days. I have been reading the “Learn Page” and have found it fantastic. I have downloaded SQL Lite Expert and have completed 2 of the four little queries, then modified them both to call up a lat and long in both . I have a query that is trying to have birthdays with birth dates and have successfully joined the info from the events table and Names however I cannot get the above query to work to convert the dates. I have been staring at it for a couple of hours and no luck including trying various things. What am I missing?
Thanks Wayne
PS What I know about programming you could put on the back of a postage stamp but I am a quick learner.
Hi Wayne,
It is string manipulation to extract the year, month, day from the yyyymmdd format embedded in the EventDate but it gets a lot more complicated to take into account the various modifiers and ranges. This page has an example script: https://sqlitetoolsforrootsmagic.com/date-decoder/