Uploaded image for project: 'OASIS Open Document Format for Office Applications (OpenDocument) TC'
  1. OASIS Open Document Format for Office Applications (OpenDocument) TC
  2. OFFICE-4094

Proposed changes to spreadsheet functions YEAR, MONTH, DAY, HOUR, MINUTE, SECOND and other related

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: ODF 1.2
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

      OpenFormula

    • Resolution:
      Hide

      Updated on 11/Sep/23 as resolved, but (25/Sep/23) subject to further discussion:

      The following text changes, previously resolved, have been rejected following further discussion. A new resolution for this issue is to be proposed.

      6.10.5 DAY

      Summary: Returns the day from a date.

      Syntax: DAY( DateParam D [ ; Logical Truncate = TRUE ] )

      Returns: Integer

      Constraints: None

      Semantics: If Truncate is FALSE, returns the day portion of D after first rounding (using ROUND()) to the nearest second. If Truncate is TRUE, returns the day portion of D without first rounding to the nearest second.

      See also MONTH 6.10.13, YEAR 6.10.23, ROUND 6.17.5

      6.10.10 HOUR

      Summary: Extracts the hour (0 through 23) from a time.

      Syntax: HOUR( TimeParam T [ ; Logical Truncate = TRUE ] )

      Returns: Integer

      Constraints: None

      Semantics: Extract from T the hour value, 0 through 23, as per a 24-hour clock.

      If Truncate is TRUE, this is equal to:

      DayFraction = (T - INT(T))

      HourFraction = INT(DayFraction * 60)

      If Truncate is FALSE, this is equal to:

      Second = MOD(ROUND(T * 86400) ; 60)

      Minute = (MOD(ROUND(T * 86400) ; 3600) - Second) / 60

      Hour = (MOD(ROUND(T * 86400) ; 86400) - Minute * 60 - Second) / 3600

      See also MONTH 6.10.13, DAY 6.10.5, MINUTE 6.10.12, SECOND 6.10.16, MOD 6.16.42, ROUND 6.17.5

      6.10.12 MINUTE

      Summary: Extracts the minute (0 through 59) from a time.

      Syntax: MINUTE( TimeParam T [ ; Logical Truncate = TRUE ] )

      Returns: Integer

      Constraints: None

      Semantics: Extract from T the minute value, 0 through 59, as per a clock.

      If Truncate is TRUE, this is equal to:

      DayFraction = (T - INT(T))

      HourFraction = (DayFraction * 24 - INT(DayFraction * 24))

      Minute = INT(HourFraction * 60)

      If Truncate is FALSE, this is equal to:

      Second = MOD(ROUND(T * 86400) ; 60)

      Minute = (MOD(ROUND(T * 86400) ; 3600) - Second) / 60

      See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, SECOND 6.10.16, MOD 6.16.42, ROUND 6.17.5

      6.10.13 MONTH

      Summary: Extracts the month from a date.

      Syntax: MONTH( DateParam D [ ; Logical Truncate = TRUE ] )

      Returns: Integer

      Constraints: None

      Semantics: If Truncate is FALSE, returns the month portion of D after first rounding (using ROUND()) to the nearest second. If Truncate is TRUE, returns the month portion of D without first rounding to the nearest second.

      See also YEAR 6.10.23, DAY 6.10.5, ROUND 6.17.5

      6.10.16 SECOND

      Summary: Extracts the second (the integer 0 through 59) from a time. This function presumes that leap seconds never exist.

      Syntax: SECOND( TimeParam T [ ; Logical Truncate = TRUE ] )

      Returns: Integer

      Constraints: None

      Semantics: Extract from T the second value, 0 through 59, as per a clock.

      If Truncate is TRUE, this is equal to:

      DayFraction = (T - INT(T))

      HourFraction = (DayFraction * 24 - INT(DayFraction * 24))

      MinuteFraction = (HourFraction * 60 - INT(HourFraction * 60))

      Second = INT(MinuteFraction * 60)

      If Truncate is FALSE, this is equal to:

      Second = MOD(ROUND(T * 86400) ; 60)

      See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, MINUTE 6.10.12, MOD 6.16.42, ROUND 6.17.5

      6.10.23 YEAR

      Summary: Extracts the year from a date given in the current locale of the evaluator.

      Syntax: YEAR( DateParam D [ ; Logical Truncate = TRUE ] )

      Returns: Integer

      Constraints: None

      Semantics: If Truncate is FALSE, returns the year portion of D after first rounding (using ROUND()) to the nearest second. If Truncate is TRUE, returns the year portion of D without first rounding to the nearest second.

      If a year is given as a two-digit number, as in "05-21-15", then the year returned is either 1915 or 2015, depending upon the break point in the calculation context. In an OpenDocument document, this break point is determined by HOST-NULL-YEAR.

      Evaluators shall support extracting the year from a date beginning in 1900. Three-digit year numbers precede adoption of the Gregorian calendar, and may return either an Error or the year number. Four-digit year numbers preceding 1582 (inception of the Gregorian Calendar) may return either an Error or the year number. Four-digit year numbers following 1582 should return the year number.

      See also MONTH 6.10.13, DAY 6.10.5, VALUE 6.13.34, ROUND 6.17.5

      Show
      Updated on 11/Sep/23 as resolved, but (25/Sep/23) subject to further discussion: The following text changes, previously resolved, have been rejected following further discussion. A new resolution for this issue is to be proposed. 6.10.5 DAY Summary: Returns the day from a date. Syntax: DAY( DateParam D [ ; Logical Truncate = TRUE ] ) Returns: Integer Constraints: None Semantics: If Truncate is FALSE, returns the day portion of D after first rounding (using ROUND()) to the nearest second. If Truncate is TRUE, returns the day portion of D without first rounding to the nearest second. See also MONTH 6.10.13, YEAR 6.10.23, ROUND 6.17.5 6.10.10 HOUR Summary: Extracts the hour (0 through 23) from a time. Syntax: HOUR( TimeParam T [ ; Logical Truncate = TRUE ] ) Returns: Integer Constraints: None Semantics: Extract from T the hour value, 0 through 23, as per a 24-hour clock. If Truncate is TRUE, this is equal to: DayFraction = ( T - INT( T )) HourFraction = INT(DayFraction * 60) If Truncate is FALSE, this is equal to: Second = MOD(ROUND( T * 86400) ; 60) Minute = (MOD(ROUND( T * 86400) ; 3600) - Second) / 60 Hour = (MOD(ROUND( T * 86400) ; 86400) - Minute * 60 - Second) / 3600 See also MONTH 6.10.13, DAY 6.10.5, MINUTE 6.10.12, SECOND 6.10.16, MOD 6.16.42, ROUND 6.17.5 6.10.12 MINUTE Summary: Extracts the minute (0 through 59) from a time. Syntax: MINUTE( TimeParam T [ ; Logical Truncate = TRUE ] ) Returns: Integer Constraints: None Semantics: Extract from T the minute value, 0 through 59, as per a clock. If Truncate is TRUE, this is equal to: DayFraction = ( T - INT( T )) HourFraction = (DayFraction * 24 - INT(DayFraction * 24)) Minute = INT(HourFraction * 60) If Truncate is FALSE, this is equal to: Second = MOD(ROUND( T * 86400) ; 60) Minute = (MOD(ROUND( T * 86400) ; 3600) - Second) / 60 See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, SECOND 6.10.16, MOD 6.16.42, ROUND 6.17.5 6.10.13 MONTH Summary: Extracts the month from a date. Syntax: MONTH( DateParam D [ ; Logical Truncate = TRUE ] ) Returns: Integer Constraints: None Semantics: If Truncate is FALSE, returns the month portion of D after first rounding (using ROUND()) to the nearest second. If Truncate is TRUE, returns the month portion of D without first rounding to the nearest second. See also YEAR 6.10.23, DAY 6.10.5, ROUND 6.17.5 6.10.16 SECOND Summary: Extracts the second (the integer 0 through 59) from a time. This function presumes that leap seconds never exist. Syntax: SECOND( TimeParam T [ ; Logical Truncate = TRUE ] ) Returns: Integer Constraints: None Semantics: Extract from T the second value, 0 through 59, as per a clock. If Truncate is TRUE, this is equal to: DayFraction = ( T - INT( T )) HourFraction = (DayFraction * 24 - INT(DayFraction * 24)) MinuteFraction = (HourFraction * 60 - INT(HourFraction * 60)) Second = INT(MinuteFraction * 60) If Truncate is FALSE, this is equal to: Second = MOD(ROUND( T * 86400) ; 60) See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, MINUTE 6.10.12, MOD 6.16.42, ROUND 6.17.5 6.10.23 YEAR Summary: Extracts the year from a date given in the current locale of the evaluator . Syntax: YEAR( DateParam D [ ; Logical Truncate = TRUE ] ) Returns: Integer Constraints: None Semantics: If Truncate is FALSE, returns the year portion of D after first rounding (using ROUND()) to the nearest second. If Truncate is TRUE, returns the year portion of D without first rounding to the nearest second. If a year is given as a two-digit number, as in "05-21-15", then the year returned is either 1915 or 2015, depending upon the break point in the calculation context. In an OpenDocument document, this break point is determined by HOST-NULL-YEAR . Evaluators shall support extracting the year from a date beginning in 1900. Three-digit year numbers precede adoption of the Gregorian calendar, and may return either an Error or the year number. Four-digit year numbers preceding 1582 (inception of the Gregorian Calendar) may return either an Error or the year number. Four-digit year numbers following 1582 should return the year number. See also MONTH 6.10.13, DAY 6.10.5, VALUE 6.13.34, ROUND 6.17.5

      Description

      Email from Mike Kaganski, 15 September 2020 reads: 

       

      As mentioned in [1], there is a problem in the OASIS OpenFormula standard (as of v.1.3 [2]). The spreadsheet functions YEAR, MONTH, DAY, HOUR, MINUTE are defined in such a way that for any given value of time, they discard fractions of respective time part: e.g., MINUTE would only return whole number of minutes contained in the parameter, discarding any fraction of a minute, no matter how close it is to 1. On the other hand, spreadsheet function SECOND is defined to round the number of seconds in minute fraction, thus it may return a number that is greater by 1 compared to the whole number of seconds actually contained in the minute fraction.

      Furthermore, SECOND is defined to return 0 through 59 (never 60), but to use formula equivalent to ROUND(MinuteFraction * 60). Here is the first minor inconsistency: ROUND(MinuteFraction * 60) results in numbers 0 through 60, not through 59.

      It is impossible to modify the definition of SECOND to return 0 through 60, because it would then become inconsistent with other spreadsheet software, introducing major interoperability problems. Correcting the equivalent formula to result in 0 through 59, like "MOD(ROUND(MinuteFraction * 60), 60)", brings us to the next inconsistency:

      ÂÂÂ For all values of TimeParam T which have (MinuteFraction * 60) between 59.5 and 60.0, the set of functions:

      Â ÂÂ ÂÂ =YEAR(T) &""& MONTH(T) &""& DAY(T) &" "& HOUR(T) &":"& MINUTE(T) &":"& SECOND(T)

      ÂÂÂ results in an ISO date-time string that is off by ~1 minute (actually, exactly by value of (MinuteFraction * 60) seconds).

      This happens because value returned by SECOND will be 0 (zero), as if the next minute has already started, while all other functions return values of the minute, hour, day, ... that are still active for T.

      For instance, for T corresponding for 2020-09-15 15:23:59.60, the result of the mentioned formula would be "2020-9-15 15:23:0".

      It is impossible to change definition of SECOND to discard fraction of a second (for consistency with other functions), because then it would again be inconsistent with other spreadsheet software. So the only solution is to re-define the other functions to be consistent with (improved) SECOND definition, like this:

      ÂÂÂ * Proposed improvement for SECOND(TimeParam T): Second=MOD(ROUND(T*86400);60)

      ÂÂÂ * Proposed change for MINUTE(TimeParam T): Minute=(MOD(ROUND(T*86400);3600)-SECOND(T))/60

      ÂÂÂ ... etc.

      This would not only make the functions consistent within the standard, but also with other spreadsheet software.

      [1] https://bugs.documentfoundation.org/show_bug.cgi?id=136615#c0

      [2] https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#__RefHeading__1018202_715980110

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              patrick Patrick Durusau
            • Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: