Logo Home    Page list

Date Formats #datadefinitions #date #sortdate

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



What triggers EditDate in NameTable?

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


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.

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.


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?


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'
AS MonthText
FROM EventTable;

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