The question I am asking really refers to any date. Can one calculate time (either in days or years) between a past date (in a date field) and today. I know this can easily be done in Libre Office Calc, but I am wondering if it can be done in Libre Office Base. I am not familar with sql, so I was wondering if this could be done in a design query or through a wizard. I am also interested in knowing how to calculate time between two date fields, and two hour/minute fields (in minutes).
I am currently trying to transfer some of my data and queries from Lotus Approach which was very user friendly for those who do not know sql. However, if this cannot be done any other way is there a Libre Office tutorial or textbook to learn sql for libre office base?
Thanks for any help you can give. The learning curve between a simple minded use of Approach and Base is a difficult one for me.
Calc is designed to perform calculations whereas a database is meant to store and retrieve data. Your date calculations can be done through SQL or in macros (much more difficult to learn than SQL).
Base itself is just a front end to a database. The database included with LO is a condensed version of the HSQL database. Base can be configured to use the full version or other DB’s such as PostgreSQL, MySQL or others. Each DB has its’ own approach to SQL but most use standards. You will find most SQL applies to most DB’s.
With that, here is the link for the FULL version of HSQL documentation (click here). It’s quite large and probably is best to be glanced through and then used as a reference. Also remember this includes more than what the HSQL included with LO can do.
For starting to learn there are many sites available - here’s one (click here).
You can use Query (in SQL view) for Select testing or from the main screen of Base from the menu Tools SQL... will also allow Update and Insert statements.
FYI here is an SQL statement to add hours and minutes in a field from all records in a table:
SELECT CONCAT("HRS","MIN") AS "TTLTIME" FROM (SELECT CONCAT(((SUM(Minute ( "Time" )) + SUM((Hour ( "Time" )) * 60)) / 60), ':') AS "HRS", MOD((SUM(Minute ( "Time" )) + SUM((Hour ( "Time" )) * 60)) ,60) AS "MIN" FROM "TimeCalc")
The original fields contain time in hours and minutes and produces a total in hours and minutes.
Edit: SQL to calculate number of years from a date to current day
SELECT DATEDIFF('year',BIRTHDATE, CURRENT_TIMESTAMP) as MYDATE FROM BIRTHDATESThanks for the answers and the references. I will see what. Too bad there is no document that includes only the HSQL used in LO.
Special thanks I got the birthdate calculation to work. Powerful stuff. What would I need to add to the state to get an extra decimal place to indicate not only that a person was 21 years old but 21.7.
Age with one decimal:
SELECT (1.0 * MYDATE / 365) AS AGE FROM (SELECT DATEDIFF( 'day', "BIRTHDATE", CURRENT_TIMESTAMP ) "MYDATE" FROM "BIRTHDATES")Thank you!