Date Formats #datadefinitions #date #sortdate

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


ve3meo

What triggers EditDate in NameTable?

ve3meo
23 January 2010 14:55:51

All mine are 0, even after editing a name.


romermb

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

ve3meo
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

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

thejerrybryan
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

ve3meo
02 July 2011 04:01:33

From a prior discussion on this page, this might be helpful:

SELECT DateTime(2415018.5 + EditDate),
strftime(‘%m’, 2415018.5 + EditDate) as MonthNum,
substr(‘UnkJanFebMarAprMayJunJulAugSepOctNovDec’, 3*strftime(‘%m’, 2415018.5 + EditDate)+1,3) AS Month,
CASE strftime(‘%m’, 2415018.5 + EditDate)
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 EventTable;

SQLite Date & Time functions are described at http://sqlite.org/lang_datefunc.html

Tom

2 Replies to “Date Formats #datadefinitions #date #sortdate

  1. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.