Add WOQL terms to support time extraction, timezone handling, and comparison

[hoijnet]

Is your feature request related to a problem? Please describe.
It is hard to process time in TerminusDB, which is crucial for business logic. And probably straightforward to add.

A significant portion of business logic, business data processing and control structures involves time calculations. My understanding is that TerminusDB likely stores UTC time internally, but accepts ISO8601-formatted timestamps for date() and datetime() with timezones that are converted to UTC time.

It's far from straightforward in WOQL to accurately compare portions the year, month, day, hour, minute, second of stored time for date, time, dateTime, dateTimeStamp, if accurate time is expected. There are a few reasons for why they should be native WOQL constructs and not string manipulations:

• Most such calculation would not be performed on UTC time, but local time.
• String manipulation is unclear to the author, and likely slower

Some example use cases:

• Select all transactions that occur the 1st of the month in Japan Standard Time (in any year)
• Show which month and day (yearly) or day (monthly) is the customer subscription anniversary
• Match transactions that happened daily between 19:00 and 22:00, and not on Saturday and Sunday

Describe the solution you'd like

Something similar to date_time_value in prolog is likely the easiest option, or to extract all the possible variants into specific terms. Notably, the weekday (starting on Sunday or Monday), and week number, is missing in date_time_value though.

So the suggestion would be to implement the following from prolog:

• date_time_value(term, dateTime, value)
• seconds_since_epoch() --> decimal
• day_of_the_week(dateTime, value)
• stamp_date_time(TimeStamp, DateTime, TimeZone)

With these primitives, it becomes possible to compare time in the data product in most circumstances.

The terms in WOQL are mostly snake_case for terms so going with that here. The above fromprolog time functions would cover most cases.

Describe alternatives you've considered
The alternative would be to retrieve the data locally and perform time calculations on the client, but would be impractical for most cases.

A relevant workaround is to determine relevant time periods to compute over on the client side, and take the max and min timestamps of a relevant range and submit as part of the query to build the range for. This works in many use cases, but makes the queries less elegant and harder to make standalone as they require client-side code to drive them.

Additional context
This is important for using TerminusDB in commercial projects.

[hoijnet]

Looks like now() already exists in WOQL but is not exposed in the clients. Could be a good addition.

[hoijnet]

After trying to understand how a WOQL term is defined, it seems the following needs to be done:

• Bind the JSON WOQL term to prolog WOQL term when executing the query
• Probably add a find_resources section
• Add test for it
• Add definition for it
• Add it to the javascript client
• Add it to the python client

There are probably more steps involved. Was curious so started looking into it as a learning exercise.

[hoijnet]

Which was the last term added, if there is a relevant similar WOQL keyword PR to look at, perhaps it could be an idea to reference as its likely almost the same work to be done for each of these new terms?

[alexander-mart]

Which was the last term added, if there is a relevant similar WOQL keyword PR to look at, perhaps it could be an idea to reference as its likely almost the same work to be done for each of these new terms?

Here is a history of recent changes to the WOQL ontology, but no new methods added:
https://github.com/terminusdb/terminusdb/commits/main/src/terminus-schema/woql.json

https://github.com/terminusdb/terminus-schema/commits/master/terminus.owl.ttl

https://github.com/terminusdb/terminus-schema/commits/master/woql-context.jsonld
from 2019-09-25 (4 years ago)

Example of adding WOQL-keywords:
terminusdb/terminus-schema@23a2e04