Date and time calculations

[docb]

New to base so please be patient!

I have diabetes and record my finger prick results in an access database I wrote a few years ago and now want to rewrite it as a base database. I am failing at the first hurdle and would appreciate some guidance.

The database has at its heart two tables, Date and Result. Date has a field "testdate" and Result has a field "time". They are related by a field "dateid". This allows me to create an entry form where I can input several results in a day, only having to input the date once. All good so far.

I now want to do some analytics on the data and the first thing is to plot my results on a timeline. In Access no problem. Create a query from the two tables with a calculated field simply adding the "testdate" field from Date to the "time" field from result giving me a DD/MM/YYY/HH/MM time stamp for any result which I can then use for graph plotting or whatever.

I cannot reproduce this in base. I have tried every combination of date field type without success and got lost down many internet rabbit holes.

The question is, can you add a date field to a time field in base? You can in calc - I've tried that - so I must be missing something. The question is what!

[robleyd]

New to base so please be patient!

As your question pertains to Base I've taken the liberty of moving it from the Impress forum, which is for questions about the presentation module (Powerpoint in M$-speak) of OpenOffice.

[Villeroy]

menu:File>New>Database...
[X] Connect to existing database
Type: MS Access
Specify your old Access document.
[X] Register database
Save database

As a first step, you get a Base document which is connected to your Access database. Notice the status bar of the Base window.

This works on Windows only. In order to use MS Access on other platforms, you need some extra steps and a special database driver.

[docb]

Thank you for your reply. I did start off by connecting base to my existing Access database but it all got very confusing so I thought i would start over again with a new, independent, Base database. Though it would be a good way to get to understand base as a newbie to it.

It leaves me with the fundamental question. Should I be able to add a "date" field to a "time" to get a date/time for an event in a single field. I've not been ableto figure it out because the simple thing, which works everywhere else , "date"+"time" returns an error in base no matter how the date and time fields are defined.

I am wondering if it is something fundamental such as a miss match between the Base front end and the HSQL database engine. Base wants - like the rest of OpenOffice - to work with dates and times as if they were numerical values either side of a decimal point whereas HSQL wants to work with Dates and Intervals.

Any further thoughts anyone.

[Villeroy]

Base is not a database development suite. It is a tiny addition to this office suite. It is most useful if you have some kind of connectable database. Connect a Base document, add some meaningful queries and use your database in the context of this office suite. Fill calculation models and serial letters with database data and turn raw data into pretty report sheets. As an extra, you can have input forms in the frugal style of the 90ies. If you are familiar with the SQL dialect of MS Access you may be perfectly happy with your original database, edit and maintain the database with MS Access while using it in the context of your free office suite as well.

Date calculation: The embedded HSQL shipped with the office suite (version 1.8 of 2005) has a function DateDiff caluclating the number of years months, days, hours, minutes or seconds between 2 date-times. http://www.hsqldb.org/doc/1.8/guide/ch09.html
A DateAdd function is desparately missing. However, you can easily extract the embedded HSQL from the Base document and use the stand-alone database with some HSQL2 driver which has DateAdd among numerous extra features.

How to convert databases from one type to the other: Create 2 database documents, one connected to the source database, the other one connected to the target database (or embedded HSQL). Copy the table icon from the source DB and paste to the tables of the target DB.

[docb]

Thank you Villeroy, just what I needed. Time to think again.

[Nick N,]

Good Day,

I would kindly ask a question to Villeroy, I.E. Does LO SQL seem self sufficient to work with dates?

Please find and example attached hereto.

With regards to docb

Nick N,

[Villeroy]

That's DateDiff. Now calculate dates 90 days ahead or before a given date.

Birthdays: viewtopic.php?f=13&t=36069&p=412463&hil ... ge#p412463

[Nick N,]

Be nice to us Villeroy. Thanks for having helped.

Regards

Nick

[Villeroy]

Date calculation with embedded HSQL 1.8.
The table is simply a list of persons with birth dates.
Query "Ages" calculates the age in years based on the DateDiff function.
Age classification shows "Child", "Teen" or "Adult".
"Plus 90 Days" calculates a date 90 days after the birth date. Unlike most other databases, HSQL 1.8 has no function DateAdd. Therefore I calculate the days since day zero 1899-12-30 and format that day number. LibreOffice date controls and formatted fields can interprete day numbers as dates just like spreadsheet cells do.
On the form, the "Plus 90 Days" field is a date control. The report makes use of a formatted field with date format.

[Nick N,]

@ docb

Should you have skills in french language, there are plenty of plain SQL examples here https://www.google.it/url?sa=t&rct=j&q= ... i=89978449

Wishful thanks to Villeroy.

Regards

Nick

[robleyd]

That link leads to the index page of the French forum, not to any specific helpful post(s). It doesn't seem very useful.

[Nick N,]

I beg your pardon robleyd. I thought docb could browse as he pleased. May I however be more precise with this one? https://forum.openoffice.org/fr/forum/v ... hp?t=67443.

With very best regards

Nick