[Libreoffice-bugs] [Bug 127477] Incomplete description of date & time functions in the help information

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=127477

--- Comment #3 from Albrecht Müller <albrecht.mueller at astrail.de> ---
(In reply to Olivier Hallot from comment #2)
> 
> 2) YEAR("1999-12-31 23:25:59,6") is not a valid argument for the function,
> but 
> YEAR(DATEVALUE("1999-12-31 23:25:59,6")) is. Besides we are 35 minutes
> before the hour in your example and it can't be the reason to round the
> year. Assuming it is a typo...

YEAR("1999-12-31 23:25:59,6") should have been YEAR("1999-12-31 23:59:59,6").
Sorry for this typo. This syntax works but it requires to set a switch in the
Calc options and the "de" locale.

> 
> =YEAR(DATEVALUE("1999-12-31 23:59:59.99999")) is 1999
> =YEAR(DATEVALUE("1999-12-31 23:59:59.9999999")) is 2000

I wanted to point to different thing: I think there is a bug in the
specification:
The specification of the SECOND function
(http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018202_715980110)
states: "Note also that this rounds to the nearest second, instead of returning
the integer part of the seconds". There is a problem in the last half second of
a minute. If you round this value, e.g. 59.6 seconds to to nearest second, you
will get 60 but the SECOND function is specified to return integer values in
the range 0 ... 59. Therefore something is wrong with this specification: If
SECOND return 60 this violates the requirement that the return value is in the
range 0...59. To ensure that the result is in the proper range you could take
the remainder of a division by 60. The specification does not contain this
operation but I think that this is tacitly assumed. Thus SECOND returns 0 in
this case.

The MINUTE function
(http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018194_715980110)
does not round to the next second. Thus a value corresponding to 59.6 seconds
is 0 minutes. Therefore 59.6 seconds corresponds to zero minutes and zero
seconds.

As all the other functions (YEAR, MONTH, DAY, HOUR, etc.) also do not round to
the nearest second it is possible to create this example where the problem
propagates up to the digit in the millennium position: So according to the
specification a value of "1999-12-31 23:59:59.6" should result in:
YEAR 1999, MONTH 12, DAY 31, HOUR 23, MINUTE 59, SECOND 0. A consistent value
would be YEAR 2000, MONTH 1, DAY 1, HOUR 0, MINUTE 0, SECOND 0. 

This example is the content of bug 127476 which was intended to demonstrate of
what I called "funny results" in comment  #1. In short: In order to avoid this
kind of inconsistencies all related date and time functions should use the same
rounding method.

> If they are not compliant, we must bring to the development for fixing.

I fear the problem appeared because the development made the behaviour
compliant to a broken specification.


> However I doubt the samples above can be considered a bug.
The line between bug or no bug seems to blur: It is confirmed behaviour that
simple date and time calculations will show pseudo random results that are
correct in 50% of the cases and one off in the other 50%(see bug 127334). I
would consider this as a bug but this behaviour arguably follows from the
specification. So this is not a bug?

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20191228/cf30bdfa/attachment-0001.htm>