I designed and created a database in PostgreSQL 14 to store information about archaeological sites occupied in Antiquity, and I am using LibreOffice Base (Version: 7.3.1.3 (x64)) as front-end, particularly to create forms to help me add and edit the data. As part of the database, I created some date fields for storing the chronology of sites, which could be BCE (Before the Common Era, or traditionally BC) or CE (Common Era, traditionally AD). This has allowed me to define a site’s chronology (for example) as “27-01-01 BC”. All seems to work in the back-end; however, when I display this field in the Base form (using format as “YYYY-MM-DD” (ISO 8601) in control properties) it doesn’t display the BC date, but instead “1890-05-31” (actually all the dates appear the same in LO Base, regardless of the actual data stored in PostgreSQL). I have read around trying to understand the problem and find a solution, and it seems this 19th century date is the earliest you can store in LO Base. However, I can’t believe it is not possible to store dates older than this. How can I solve this issue?
Try a text field or a formatted field with date formatting. The latter is like a spreadsheet cell.
Not directly related, but FYI, as a note of warning about possible future fix, that could affect dates that you are working with. Note that currently, the dates in LibreOffice are represented inconsistently, using Julian (and proleptic Julian) date representation for dates prior to 1582-10-15. See tdf#144699.
Thanks for this solution, @Villeroy. Sadly, I tried them both and neither works:
- when trying the text field the date is displayed as a negative number (400 BC becomes -547862, and 50 AD becomes -675696).
- As for the second solution, when I apply the formatted field with date formatting, 400 BC becomes 0399-12-31 (no sign of the BC) and 50 AD becomes 0050-01-01. It seems also that when dates are BC I lose a day in the process. I tried modifying the pattern of the date formatting to add the BC (YYYY-MM-DD" BC"), but what it does is to add the BC as a constant, regardless of the date being BC or not. Is there anyway to include the " BC" as an option instead of as a constant? (A clarification: date is PostgreSQL stores dates BC as “YYYY-MM-DD BC”, whereas dates AD “YYYY-MM-DD”, no AD added at the end)
How did you create a date field, which contains
Could test a little bit with PostgreSQL and Base, but don’t know the code to create such a date field.
Calculate the AD and BC in a separate field. Something like
SELECT …, CASE WHEN Year(“Date”)<0 Then ‘BC’ ELSE ‘AD’ END AS “Era” FROM …
Have a look at this spreadsheet with its custom number format in the first column.
t78449.ods (20.0 KB)
Thanks, @RobertG! Here it is:
create table site (
id integer primary key,
name varchar,
start_date date);
insert into site (id, name, start_date)
values (1, 'Site1', '0027-01-01 BC'),
(2, 'Site2', '0050-01-01');
Hope this helps (?).
Thanks, @Villeroy. I used the formula in the field and (regardless of the original date) all dates became “AD”. Base is crashing a lot, too, and I can´t find a work-around to losing 1 day in the BC dates (400 BC becoming 399-12-31 BC)… any other thoughts?
PS: I am SO appreciating the help… thanks a lot.
You can use the following constructs to display data:
select to_char(cast(start_date as timestamp), 'YYYY-MM-DD BC') from site
What does a day mean after all those years?
There are different bugs with dates and Base GUI:
In a form fields for dates won’t allow a date older than ‘1800-01-01’. So you have to use a formatted fields to add older dates than ‘1800-01-01’.
There will always be a difference of 2 days between the date the control will show and the date, for example, could be get by Tools → SQL.
So: Solution from @sokol92 will work for showing data with right date. Input of a new date will only work when adding 2 days in the form control (formatted field).
First differences between PostgreSQL and Base: Gregorian Calendar, where Thursday 4 October 1582 was followed by Friday 15 October 1582.
Base would jump to default 1899-12-30 as ‘0’ if there is chosen any date between 1582-10-04 and 1582-10-15. PostgreSQL will accept these dates, but GUI of Base won’t. But this couldn’t be the only reason, because the difference, which appears at this moment, is about 20 days between entry in the date field and saved date value.
Try this for the code @MCLands . Connect the database to PostgreSQL …
Postgresql_site.odb (14.2 KB)
Will change the date in a masked field, which is loaded from a query @sokol92. Macro is bounded to “Before record action” in the form.