LibreO-Base is lying about my age!

users
#1

--- Alkuperäinen viesti ---
Aihe: LibreO-Base is lying about my age!
Päiväys: 15.2.2012 17:46
Lähettäjä: Pertti Rönnberg [ptjr@elisanet.fi]
Kopio: Pertti Rönnberg [ptjr@elisanet.fi]

Dear all of you,
First an opinion - correct me if wrong!
I've got the idea that this is kind of a help-desk where every LibreO user can both ask for help and give info about problems related to LibreO's components. Without any bad feelings afterwards!
Referring to the irrelevant "debates" recently and also my own experiences some weeks ago I would say that a real expert has the ability to concentrate on the question as-it-is and give a relevant answer -- he has knowledge enough to understand that there are no silly or wrong questions.
Every question has its reason - and every question is valuable because it indicates that there may be something to repair or do better in LibreO.
There is a truth in quality systems (e.g. ISO 9000): "without identifying the weaknesses there is no development".

_And now to my contribution to make LibreO better:_
I need to calculate in LO-Base the difference between an 'olddate' and today expressed in whole years (no decimals)
The main procedure seems to be the same in LO-Base and OOo as in MSAccess.
The function I placed in the query field is (ref. also to guides in LO & OOo):
     datediff('year',"table1"."olddate",current_date) or datediff('year',"olddate",current_date)

When I inserted my birthdate (2.11.1939) in 'olddate' my computer states that I am 73 - I'm NOT, I'm still only 72!
The correct result is 72,225 years; and the desired answer without decimals 72.
> I got the same wrong result (73) both in LO-Base and in OOo-Base
> the wrong result is not logical - it can not be a rounding up, nor is it a result of an 'int()'-function
> if I replaced 'year' with 'dd' or 'mm' or 'yy' the function works -- but
when replaced 'year' with 'yyyy' the function stopped working (why - 'year' and 'yyyy' ought to be equal!)
> the query field does not accept the expression "int(datediff(..etc))"
> the date-format is equal (finnish - see above) in every relevant field
> my OS is Windows7Professional/32Bit

I also tried the formula /year(current_date - "olddate")/ but the query did not accept it as a valid expression - can I put that formula anywhere else?

Anyone there who can tell me how I shall get the LO-Base to be honest?
Regards
Pertti Rönnberg

#2

SELECT "Table".*,
DATEDIFF( 'mm', "BirthDate", CURRENT_DATE ) / 12
- CASE WHEN MONTH("BirthDate")=MONTH(CURRENT_DATE) AND
DAY("BirthDate")>DAY(CURRENT_DATE) THEN 1 ELSE 0 END AS "Age"
FROM "Table"

#3

Hi :slight_smile:
The "integer" function in Access rounds down.  So that 72.9999 would also be given as =72.  Clearly in most cases (such as when looking at cash prices) the usual answer you would want it to give would be 73, which is what Base appears to be giving.  Annoyingly it is also 'incorrectly' rounding up for a value that 'should' be being given as 72.

In Access i tended to add 0.5 before doing the integer thing in order to force it to round correctly.  In Base perhaps just do a -1?  or a -0.5?  Of course i found the "Round" function in Access after writing the equations but i don't think you want to risk rounding up.

On a side note i find it interesting that up until legal drinking age children tend to say things like "I'm nearly 9" or "I'm 12, ('soon')" but after legal drinking age people try to down-play their age.  Also we say "I'm ... years old" rather than "I'm ... years young".

Also i heard that some cultures respect their elders, partly for just surviving that long, but also for the acquired wisdom that a person is likely to have acquired.  Surely we should celebrate our age?
Regards from
Tom :slight_smile:

#4

The problems seems to be that DATEDIFF rounds the result to an
integer rather than just ignore digits to the right of the decimal
place.

--Dan

#5

Andreas - thank you for answering.
You certainly noticed that in my mail was two (main) issues:
1. >> I asked for help with a database problem when LibreO-Base's basic function (DATEDIFF('yyyy'...)) calculates a wrong result;
I hope that you realize how important and severe that matter is both for LibreO and for every user: if a user can't trust the calculations then better not to use LibreO-Base.
2.>> I pointed out that there must be a fault in the the function's programmed construction when the function requires the string 'year' in stead of 'yyyy' the latter form is the modeled in 'official' guides. They ought to be interchangeable.

Without answering or commenting the two points above, you sent to me a SQL command that - as to my experience - is bullshit
>> it did not work in my database so I started to test it
>> I made a simple database backwards from your SQL command: one table "table" with tree fields (id, birthdate, age), inserted one record, let the query wizard make the query and copy-pasted (note!) your SQL command into the criterion field in column 'age' -- it did not work
>> neither did it work as an expression in an 'extra' field(column)
>> your SQL command had syntax errors both in the command build and the characters used (e.g. ",', [])
As you may understand from my message I had myself already tried to build up a SQL command similar to yours - but I was not good enough in SQL.

Question:
Does LibreO recognize expressions? if so, what is the syntax?

And then a little psychology
In an recent other thread was a discussion about how to get more people to use databases - 'how to lower the barriers'. One point of view seemed to be that ordinary non-professional users feel it too difficult to design dbs and then also to use them. What do you think if considering my writings above?

I really want to think that you are an expert on databases - show it!
Please, try again and give me an adequate answer on my first problem.
Without wasting any more of your time.
Pertti Rönnberg

_PS: ref to Tom's and Dan's kind answers today_
Mathematics is an international language with it's commonly fixed rules.
And the only relevant answer is that the calculations in LibreO-Base (and Calc!) really follow the math's rules!
As I mentioned in my mail: the result is not logical. An INT() function reduces 72,225 to 72(,oo...) and there is no excuse in the world to round 7,225 to 73!
I am serious! I do use both Calc and Base in matters where the correct calculation is important.
I would not want to accept LibreO (nor OOo?) as a kindergarten play!
pr

#6

The problem has nothing to do with LibreOffice. It is a built-in function of
the HSQL database engine. I am sure that it is designed to work like this.
'yyyy' is not a valid argument for the datediff function.
This is the home page of that software: http://hsqldb.org/
Version 2.2 is documented here: http://hsqldb.org/doc/guide/
Version 1.8 is the "integrated" database engine of LibreOffice. Its
documentation seems to be gone by now.

#7

Here is a link for version 1.8:
http://www.odfauthors.org/openoffice.org/english/userguide3/db3/hsqldb-guide-pdf/view

    Uploaded a copy.

--Dan

#8

Andreas Säger <villeroy@t-online.de> writes:

The problem has nothing to do with LibreOffice. It is a built-in function of
the HSQL database engine. I am sure that it is designed to work like this.
'yyyy' is not a valid argument for the datediff function.
This is the home page of that software: http://hsqldb.org/
Version 2.2 is documented here: http://hsqldb.org/doc/guide/
Version 1.8 is the "integrated" database engine of LibreOffice. Its
documentation seems to be gone by now.

Actually, it's at http://www.hsqldb.org/doc/1.8/guide/

#9

Andreas,
Thanks again for your answer - nothing to argue about because the matter is secondary.
I had already read the HSQLDB v2.2 guid that you refer to. And as far as I can understand the 'yyyy' ought to be as sufficient as 'year'. Here is a copy that confirms it:

/Table 10.1. TO_CHAR, TO_DATE and TO_TIMESTAMP format elements
RRRR 4-digit year
*YYYY 4-digit year*
YY 2 digit year/
Means that LO-Base shall definitely not stop working if 'yyyy' is used -- which is also the normal format in most other calculating programs.
And surely it has to do with LibreO(-Base) if it is a feature of HSQLDB that is embedded in LibreO -- I did load LibreO not HSQLDB.
Go and take a look at your car. The car itself runs perfectly well without a steering wheel and the seats, but you as the user (driver) do need them; that is why they are installed (embedded) in the car.

You did not focus on my main issue: /_how to get a correct calculation result._/
Now you are wasting not only your time but also mine.
I remain waiting
Best regards
Pertti Rönnberg

#10

Functions TO_CHAR, TO_DATE and TO_TIMESTAMP convert between strings and date
values and accept 'yyyy' as part of the string format specification so the
function gets a hint that the text assumes dd/mm/yyyy format, yyyy-mm-dd or
whatever. These functions are unavaillable in the built-in HSQLDB 1.8

This is the documentation of the DATEDIFF function:
DATEDIFF

DATEDIFF ( <field>, <datetime value expr 1>, datetime value expr 2> )

<field> ::= 'yy' | 'mm' | 'dd' | 'hh' | 'mi' | 'ss' | 'ms'

The function always rounds up to full integers stating that you are in your
73th year. Knowing this, you can calculate the common age (completed years)
with my little formula.
It gets the completed years from months divided by 12 and if the months are
the same it tests if the today is before or after the birthday.

#11

Howdy Pertti,

  datediff( 'mm', "dob", CURRENT_DATE ) / 12

works for me.

Best wishes,

//drew

#12

Hi:

  I suggest to do this:

  If

  A1: 2/11/1939
  B1: =Now()
  
  Input in A3:

  =ROUNDDOWN ((B1-A1)/365)

  outcome: 72

Regards,

Jorge Rodríguez

#13

Dennis, Jorge and Drew - thanks!
The functions rounddown() and yearfrac() are both new to me; I shall study and try them.

Drew,
I think that I have got my problem solved - I'm satisfied.
Not necessary, but of pure interest I should like to know what is wrong either in the writing of Andreas' solution or elsewhere - when copy-pasted into a db (LO-Base v.3.4.5) a pop-up window says first "column 'Select ..(etc)' is unknown" and then "syntax error".
The Andreas' version was (directly copy-pasted here):

SELECT "Table".*,
DATEDIFF( 'mm', "BirthDate", CURRENT_DATE ) / 12
- CASE WHEN MONTH("BirthDate")=MONTH(CURRENT_DATE) AND
DAY("BirthDate")>DAY(CURRENT_DATE) THEN 1 ELSE 0 END AS "Age"
FROM "Table"

Regards
Pertti Rönnberg

#14

DAY(date) does not exist as a built-in function when using LO. What
should work in your case is DAYOFMONTH(date). This returns the day of
the month (1-31) for the date entered.
     Another item, "Table".* tells the query to return all fields of the
table. You don't need to include this in the query. This is a summary
query and limits what fields can be used in this query. If you don't
know what this is, download Getting Started with Base chapter in the
Getting Started Guide from
http://www.libreoffice.org/get-help/documentation/.

     More likely what you need to use is:
SELECT DATEDIFF( 'mm', "BirthDate", CURRENT_DATE ) / 12
- CASE WHEN MONTH("BirthDate")=MONTH(CURRENT_DATE) AND
DAYOFMONTH("BirthDate")>DAYOFMONTH(CURRENT_DATE) THEN 1 ELSE 0 END AS
"Age" FROM "Table"

--Dan

#15

SELECT "Table".*,
DATEDIFF( 'mm', "BirthDate", CURRENT_DATE ) / 12
- CASE WHEN MONTH("BirthDate")=MONTH(CURRENT_DATE) AND
DAY("BirthDate")>DAY(CURRENT_DATE) THEN 1 ELSE 0 END AS "Age"
FROM "Table"

I tested that query literally with a table named "Table" having a date
column named "BirthDate" in both parsed mode and in direct SQL mode. It
selects all the table columns plus the calculated age.
I used the embedded HSQLDB 1.8 in LibO 3.3.4 under Linux.

#16

I created a new database from scratch and copied the above query string
without modification.
This is the resulting file:
http://www.mediafire.com/file/lsx9jxlesvhw7vd/AgeCalculator.odb