Is 1900-01-01 an epoch in Base and/or HSQLDB?

Debugging tdf#167591, I found a variable named STANDARD_DB_DATE, with value of 1900-01-01. It is quite problematic, differing from the most-widely-used value of 1899-12-30. This results in all sorts of ambiguous situations, when it’s not known where the value comes from, and so if a correction is needed.

I want to change STANDARD_DB_DATE to 1899-12-30. Does anyone know, if its old value is somehow exposed to API? Do you ever come across it, when writing your Base macros? Thanks!

1 Like

Having a record set with today’s date 2025-07-19 in column 1 of a HSQLDB (version 2.4), I can query several data types from that column.
getString(1) → 2025-07-19
getDate(1) → c.s.s.util.Date 2025,07,19
getInt(1) → 45855 which is Calc’s day number of 2025-07-17.
same with getDouble, getLong, getFloat.

This is contrary to a formatted form field bound to the very same date column. When I switch the formatted field to number format 0, it displays 45857, which is the “correct” day number based on NullDate 1899-12-30.

I’m not sure how many macro coders work with numeric representations of database dates. A possible scenario in StarBasic:

x = oRecordset.getLong(1)
y = cLong(Today())
If x-y > 21 then Msgbox "This invoice is more than 3 weeks overdue"

This would be wrong by 2 days and your fix would also fix the macro code.

P.S. same with embedded HSQL, embedded Firebird and HSQL 2.4.

1 Like

Done in commit 5b808448dc79e996efa7cb5082145d4e5803daa6.

2 Likes

@mikekaganski
Does the comment in the dbconversion.hxx file also need to be corrected?

        // return the date from the numberformatsupplier or the STANDARD_DATE (1900,1,1)
        OOO_DLLPUBLIC_DBTOOLS css::util::Date getNULLDate(const css::uno::Reference< css::util::XNumberFormatsSupplier > &xSupplier);

3 Likes

Done in c23a560f4b524b1c88bd2106830bad8d956f232f - thanks!

I’m wondering whether @erAck might be able to add something as to why we had a different null date for Base compared to Calc, but then again, it might well be lost in the mists of the StarOffice era, and the not so well documented choices/assumptions of that time.

Im currently not a user of local databases, but I risk to post an opinion:
We should do away with all those “epochs” and “nulldates”. Each one is worse than the others (though the Star Calc 1.0 nulldate of 1900-01-01 was a tiny bit cleverer than the others), and confusion is unavoidable . Dates are dates, not “serial” or “ordinal” numbers. For dates there is the ISO 8601 textual representation, and we dont need anything else for storing dates and working with them. Where needed zone information can be included.
Remains the conversion of existing databases as a problem, of course.

But we have also other calendar schemes in asia or the muslim countries for example. And on the other side of this planet we can have two different dates “side by side” at the same time, so scheduling a phone call can be complicated across the international date line.
.
Some problems are easier to solve with a numeric date (besides faster calculations).

There are very few things about which I actually know the relevant facts, but, of course, I didn’t post to this topic without knowing about the facts an opinions you mentioned.
However, your statement

should be seen as another reason to consider my position. No regional, local, religious, culture-neutral, … calendar can help with this. It’s a “pure problem” without any attempt to become a solution. And if a “point in time” must be negotiated, complete UTC date-time in ISO conforming notation is the means of choice.

An as-if-solution being “easier” is a bad idea if it increases the risk of errors.
Correctness before “efficiency”!
Concerning the Gregorian calendar see also this link.

My two cents, though quite off-topic.

You should never forget that any piece of data has at least two representations (or usages):

  • an external one, presented to humans, in a culture-bound readable form
  • an internal one for storage or manipulation

The case of dates is the most problematic. Astronomers have solved the issue by adopting the Modified Julian Day (which has nothing to do with the Julian Calendar, loosely related). Events receive their MJD number as a kind of absolute marker. This number can be converted to any current calendar.

GRAMPS, a FOSS genealogical program, uses a modified MJD (essentially shifting day switch to midnight instead of noon to avoid the ambiguity caused by the switch in the middle of the day). This internal representation never shows up. Only the value in the chosen calendar (Julian, Gregorian, Jewish, Swedish, Muslim, French Republican, …) is ever displayed.

The MJD epoch is sufficiently far back in the past to result in positive number for any meaningful event. And a full Julian cycle, 7980 years-long, can largely be encoded in a 32-bit integer.

So, since MJD has been stress-tested by astronomers (and they know what time means), why not use it in our DBs? There are fast and efficient formulas to convert to any calendar.

2 Likes

PostgreSQL uses it.

Heh. Do we know what calendar we want to use?
In LibreOffice, we still have the odd Julian calendar for display for pre-Gregorian times. There are lots of issues arising from that.

Then, we chose the “there is no year 0” idea. But e.g. SQLite uses proleptic Gregorian with year zero (preferred by astronomers, by the way). No idea what PostgreSQL does in that regard, though. Python decided that there was no life before CE. Should we re-consider that, when choosing the algorithm?

And most of all: which specific problem are you trying to solve? OK, you decide to use e.g. Julian Day-based count in LibreOffice at runtime. (By the way, “7980 years-long” is utterly insufficient for us, where we use years through 32767 - you may say that it’s not required, but changing that would be a regression for many). OK; but how does that change what is stored in database? in documents? How these different bases interact? Or do we break all existing documents, and ignore interoperability with other implementations?

By the way, “having fast and efficient formulas” to convert e.g. to Jalali calendar (which, by definition, is not algorithmic) is unlikely.

There are lots of interesting questions around chronology. But there is no definite, single correct answer for any of them. I see all the side-stories here as just wishful thinking. The small change I made improved one small detail. A large change will most likely not improve anything.

1 Like

Base is a bridge between all kinds of connectable databases and office documents. Whatever the database driver returns as a date value, it needs to be converted into a formatted number for

  • text table cells
  • spreadsheet cells
  • mail merge fields which support the same number formattings as text tables and sheet cells.
  • form controls, mainly formatted fields and date fields
  • report’s “text fields” which are mis-labeled formatted fields, actually.
  • the table grids showing raw data in a Base window or data source window (same number formats)
  • com.sun.star.sdbc.XRow.getInt/getLong/getFloat/getDouble

For now, LO uses integer day numbers based on day zero 1899-12-30.

1 Like

I don’t know, I wasn’t involved. Maybe it was MS-Access related that might have a 1900-01-01 null-date, I don’t know.

1 Like

How can we get rid of all this tiresome nulldate nonsense?

But I think this is up to each DB, isn’t it?
Besides: how calculations could be done in code?
Calc must be compatible with the “standard” = Excel, so etc…
DSeriais

How did you do this?

Reimplement all date+time handling, introduce a date+time cell type in Calc and elsewhere, reimplement calculations with date+time, reimplement number formatting for date+time, write conversions from/to alien file formats like Excel and various other that use a null-date and numeric serial day values (which originally was the reason we have some null-date). Fix the hundred bugs that will pop up for corner cases and unforeseen obstacles. Good luck.

1 Like

Did it that day when this showed up.
DB (Firebird) is dummy of course. Used it to implement a sub-form filter without the Filter Table method.

How did you do that? I ask specifically what code can be used to see this. Because your screenshot shows, that my change must be reverted.