Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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.

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 BIRTHDATES