Time and DateTime types and duration

[wojciechczerniak]

Description

While removing #118 moment.js dep, we've removed the support for DateTime and Time data types. We have to bring back the support for those data types.

Time [1] is defined as "Time is a subtype of Number. Time is represented as a fraction of a day."
DateTime [2] is defined as "DateTime is a subtype of Number. It is a Date plus Time."

Is returned by some function: NOW, WORKDAY, VALUE.

Time is parsed from strings: "HH:MM:SS AM/PM", "HH:MM AM/PM", "HH:MM:SS", "HH:MM"

Duration is often a result of arithmetic operators between time values.

Evaluators shall support time values in at least the HH:MM and HH:MM:SS formats, where HH is a 1-2 digit value from 0 to 23, MM is a one- or two-digit value from 0 to 59, and SS is a one- or two-digit value from 0 to 59. The hour may be one or two digits when it is less than 10. VALUE converts time values into Numbers ranging from 0 to 1, which is percentage of day that has elapsed by that time. Thus, VALUE("2:00") is the same as 2/24. Evaluators should accept times with fractional seconds as well when expressed in the form HH:MM:SS.ssss...

Links

[1] https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__1017886_715980110
[2] https://docs.oasis-open.org/office/OpenDocument/v1.3/csprd02/part4-formula/OpenDocument-v1.3-csprd02-part4-formula.html#__RefHeading__74713_1363921367

[izulin]

closed? #276

[wojciechczerniak]

Unfortunately, the PR was merged before I've managed to check it. I don't know where we are with this and I don't see tests for some use cases so we will have to wait for @aninde.

It should be possible to recreate all formats shown here: https://support.office.com/en-us/article/TEXT-function-20D5AC4D-7B94-49FD-BB38-93D29371225C with stringifyDateTime and a library like moment.js ?

Time

Date

What about the other way around? When do we use parseDateTime callback? Can we provide all the mentioned formats in the dateFormats and have them correctly recognized? Then parsed with moment.js or date-fns packages?

---

BTW I think I've found an issue with the TEXT function. It is in DatePlugin, while it can format a lot more than date or time.

Fractions

Percentages

Maybe the stringifyDateTime and parseDateTime are not the right answer? 🤔 Maybe we need something a lot more generic? Parse any string or return it as is?

[izulin]

@wojciechczerniak
I think that roughly whatever was requested by this request was covered in #276 (to be verified, but see tests here: https://github.com/handsontable/hyperformula/blob/develop/test/date.spec.ts )
Other issues that you mention (which are certainly interesting and non-trivial) here are outside of scope of this issue, and deserve their proper separate issues.

[izulin]

and tests here: https://github.com/handsontable/hyperformula/blob/develop/test/interpreter/function-text.spec.ts

[wojciechczerniak]

I have created an issue for things that were out of scope

You can find it here: #314. I agree we should focus on Time, DateTime and Duration here.

Not all format options are supported

For AM/PM we've got A/P but we should support AM/PM as well.

XL:

We're missing fraction of seconds

ODFF:

Evaluators shall support time values in at least the HH:MM and HH:MM:SS formats, where HH is a 1-2 digit value from 0 to 23, MM is a one- or two-digit value from 0 to 59, and SS is a one- or two-digit value from 0 to 59. The hour may be one or two digits when it is less than 10. VALUE converts time values into Numbers ranging from 0 to 1, which is percentage of day that has elapsed by that time. Thus, VALUE("2:00") is the same as 2/24. Evaluators should accept times with fractional seconds as well when expressed in the form HH:MM:SS.ssss...

Libre:

XL:

We're missing the duration format (square brackets [ ])

Libre:

XL:

Note that the default format contains the brackets, hours can be larger than 00-23, therefore, allowing to input a duration:

[wojciechczerniak]

@izulin Where are we with the missing parts? ☝️

[izulin]

Should be done by the end of the week.

[wojciechczerniak]

Finished in #342 👍