I am using Calc and Base for my ancestry research. I have reached a point where dates earlier than 1583 are required.
Short answer: yes.
Long answer: Libreoffice stores date values as a number. The number represents the number of days from an arbitrary reference date. In the case of Libreoffice calc, the default reference date is 30 Dec 1899. Dates earlier than this are simply stored as negative numbers. Other options for reference dates can be chosen under Preferences > LibreOffice Calc >> Calculate.
I’m impressed that you have been able to trace your family back this far.
Are you aware that there are specific applications for genealogical projects? One free, cross-platform and open-source example is Gramps (https://gramps-project.org).
(This answer only is about Calc. I am not enough experienced with Base and things may depend there on the underlying wrapped-in database engine or the one connected.)
Probably the OQ worried about the Gregorian calendar reform. Nearly everything will also work with older dates.
Details:
In fact the DATE function does not accept parameter values referring to days earlier than 1582-10-15 the first day of the new calendar. You may, however, enter also 1582-10-04 the last day the reform was not in effect. The difference of the numeric values of the two days will be 1 then which is correct. (=DATEVALUE(“1582-10-15”) - DATEVALUE(“1582-10-04”) will return 1.) DATEDIF is also applicable. “Undates” like 1582-10-11 will not be recognised as dates when entered but will be treated as text. DATEVALUE will return Err:502 in the case. The ISLEAPYEAR function is not specified for dates before 1582 though it will not return an error but wronly apply the Gregorian rule (last tested with V5.2.2)
Plaese note: The central ISO 8601 conforming date format using the format code “YYYY-MM-DD” in many locales, (displayed using the example 1999-12-31) does not correctly apply the mentioned standard for dates in year 0000.) Supposing that the application of the proleptic Gregorian calendar was intended, year 0000 should be accepted and years outside the range [0;9999] should either throw an error under the mentioned format or automatically be transferred to “+YYYYY-MM-DD” applying the sign only to the year part. Fortunately the two of us do not know ancestors from year 0 or earlier.
Gentlemen : many thks for your very quick replies. Several points …
-
I was wrong I think in assuming that LO Calc would be the same as LO Base. I did have trouble with dates in Base (see below),
but now find that as far as I can tell, Calc accepts dates earlier that 1582 with no issues. Sorry for my mistake in this aspect. -
After your posts I tried again in Base. I was using a Form, and had problems with early dates. Now I find that if I go back to the original
table in Base, from which the Form derives its data, the table will accept dates earlier than 1582, as it seems does Calc. -
The problem however persists with the Base Form. It will not accept dates earlier than 1600. In the Control(Properties) function in the
Form Design view, there are fields where minimum and maximum dates can be set. No matter what I input into the Min Date field,
it reverts to 1600. Dates earlier than this appear to be impossible in the Form, despite the fact that the source table will accept earlier
dates. -
Before posting my original query I did consult several web pages that document LO functions,(sorry I forget the sources), and there was
documentation to indicate if I understood them correctly that Date has a minimum that corresponds to 1583. So I was pleased to learn
from you that earlier dates can be used in the tables.
So once again many thanks.
I will be pleased to hear from anyone who knows about the Form issue.
DocTom.
(PS> I do know about applications for genealogy data, but I rather fancied doing my own - I am a retired old so and so, and I like to try
my hand at new things.)
(I still am not an experienced “designer” of databases but…)
The Form should actually be LibO while ‘Base’ is not “The LibO DB”. but a tool to either connect to standardised SQL databases or to use a database program developed idependently but embedded with LibO in a way capable of creating stand-alone-databases (no server needed).
Presently (>= V5.0) LibO comes with HSQLDB (still) and also Firebird database (exp.).
There may be differences concerning the range of valid dates.
I could not find an explicit specification of the type ‘Date’ including a minimum range for SQL in general.
The MS SQL Server supports “January 1, 1753, through December 31, 9999”.
Different database servers/engines may support different ranges.
I could not yet find a specification for HSQLDB dates.
For Firebird I found “1 Jan 100 CE to 28 Feb 32768 CE”. (32-bit date, 64-bit date-time)
Once again: This is not a genuine LibO topic.
Lupp : your knowledge seems to be vast. I think that I get your point about Base not being a genuine LO topic, but I do see the following on the LO website " Base is a full-featured desktop database front end, designed to meet the needs of a broad array of users. Base caters to power users and enterprise requirements, providing native-support drivers for some of the most widely employed multi-user database engines: MySQL/MariaDB, Adabas D, MS Access and PostgreSQL." Perhaps it is on the margin?
My initial understanding of date in LO was from LO Help - DATE(Year; Month; Day). Year is an integer between 1583 and 9957 or between
0 and 99.
This seemed to correspond with earlier comments, but I was pleased to be informed that tables can use earlier dates.
“Perhaps it (‘Base’) is on the margin (within LibO or not)?”
I obviously expressed myself unclear. ‘Base’ is an integrated part of LibO. However, it is not a database Server/Engine/Everything but a ‘Front End’. In addition Libo presently comes with two integrated database programs, one for a longer time now ('still") and one experimental. Insofar we may regard LibO as also providing a kind of database server not accepting external connections.
In short: ‘Base’ can connect to SQL databases of different brands. As far as I know there is no way to use a different front end to connect to a database created with a program embedded into LibO and opened again with LibO. It’s “one way”. There may be, of course, different applications capable of working with .odb files.
Just a comment on the Base dates. The only problem I found was using the “Date” control. It wouldn’t allow dates earlier the 1800. Changed this to a “Text Box” control and entry/view work fine:
In the lower TESTDATE vale is the same date field using a “Formatted” control and it displays date value (prior to 01/01/1900). Used this value in calc and the date returned was 12/24/1118.
I ran the test using the embedded DB (HSQL 1.8) and with a split DB using HSQL 2.3 and both results were the same. I can’t imagine other DB’s would be diferent.
Many thks indeed. If I understand you correctly, it is necessary to change the field in the LO Base table to a “Varchar” type
(from “Date” type), When I do this my existing form (emphasize form) will still not accept anything before 1600 (not 1583) so I must create a new form. This appears to work. Am I understanding your comment correctly?
DocTom.
No. The table stays as type Date. On mine, I only changed the table field to format as “MM/DD/YYYY”. The FORM control needed to be changed from “Date” to “Text Box” which is a simple right click on the control. Then select “Replace with” text box. No need for new form.
Ratslinger : you are superb! I followed your guide and I now have everything working great. Very many thanks indeed to you, and all those
who posted help.
DocTom.