Ask Your Question
0

Can one calculate age from a birthday field in Libre Office Base?

asked 2016-07-30 20:41:54 +0200

Quarto Die gravatar image

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-07-30 21:51:51 +0200

Ratslinger gravatar image

updated 2016-07-30 22:36:23 +0200

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
edit flag offensive delete link more

Comments

Also look here for LO documentation: (click here).

Ratslinger gravatar imageRatslinger ( 2016-07-30 21:55:06 +0200 )edit

Thanks for the answers and the references. I will see what. Too bad there is no document that includes only the HSQL used in LO.

Quarto Die gravatar imageQuarto Die ( 2016-07-31 02:46:02 +0200 )edit

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.

Quarto Die gravatar imageQuarto Die ( 2016-07-31 05:11:51 +0200 )edit

Age with one decimal:

SELECT (1.0 * MYDATE / 365) AS AGE FROM (SELECT DATEDIFF( 'day', "BIRTHDATE", CURRENT_TIMESTAMP ) "MYDATE" FROM "BIRTHDATES")
Ratslinger gravatar imageRatslinger ( 2016-07-31 13:36:01 +0200 )edit

Thank you!

Quarto Die gravatar imageQuarto Die ( 2016-08-02 01:01:13 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-07-30 20:41:54 +0200

Seen: 931 times

Last updated: Jul 30 '16