Using date-time operations in TypeQL queries

[haikalpribadi]

Problem to Solve

For a given match clause:

match $x isa person, has birth-date $bd; $t isa tax-year, has application-date $ad;

We would like to perform some date-time operation on the date-time attributes. For example, to identify $age as "duration":

$age = $bd - $ad;

The list of date-time operations we'd like to perform are:

• subtraction of date from date -> duration
• addition of date and duration -> date
• rounding a date to the nearest month -> date

Current Workaround

At the moment, users have to compute these operations manually and store them into the database.

Proposed Solution

Similar to arithmetic operations (#5521), we introduce a special type of statement to perform date-time operations; let's say "functional statements". This would allow us to write patterns (in match clauses and rule bodies) such as:

$x isa person, has birth-date $bd; $t isa tax-year, has application-date $ad;
$age = $bd - $ad;

Additional Information

As a first iteration of the solution, we can prohibit variables that contain results of date-time operations to be used further for comparisons.

[amaster507]

I think you will also need datetime functions such as other popular database query languages have. Functions to get date parts such as Day of Week, Year, Month, Date, Day of Month, Hour, Minute, etc. from the datetime field for comparing and computing with one another.

[lveillard]

An example use case:

Match posts made by someone on their birthday:

match
$authorship ($author, $post)
$author isa author has birthday $author-bd
$post isa post has postedDate $posted-date;
Month($author-bd) = Month($posted-date)
Day($author-bd) = Day($posted-date)
get $post

[lveillard]

As a wider solution (not only for dates), we could create a client-made function library where people could define some simple functions (similar to dgraph lambdas)

For example the user could create the function SameDate(datetime1,datetime2) => boolean in rust/js/go...

and use it like this:

match
$authorship ($author, $post)
$author isa author has birthday $author-bd
$post isa post has postedDate $posted-date;
SameDate($author-bd ,$posted-date))
get $post

Of course this would add some risks, and it's complex to code. But would surely open huge doors to lots of use cases

[krishnangovindraj]

Arithmetic is being implemented (#5521, #6654) with functions which support datetimes as input. The machinery for this will thus be implemented and this feature boils down to implementing the required functions. Keeping it open as a check to implement the required functions.

[thomaschristopherking]

If it's not too late, it'd be good to have a function to get the current date-time. A lot of common use-cases. Like, getting all of the contracts that haven't expired. Setting a created time to now. Setting an expiry time to now + 2 years. Etc.

[krishnangovindraj]

@thomaschristopherking Would it be acceptable if this function (and any such function which is influenced by external factors) has a fixed value throughout the transaction? Without that restriction, we have a problem with reasoning because the truth value of the time assignment becomes very dodgy. ?a = now(); will be true for a certain value of ?a, and a different one 5 minutes later.

We'd name it something which reflects this fact, such as "transaction_start_time();"

[thomaschristopherking]

@thomaschristopherking Would it be acceptable if this function (and any such function which is influenced by external factors) has a fixed value throughout the transaction? Without that restriction, we have a problem with reasoning because the truth value of the time assignment becomes very dodgy. ?a = now(); will be true for a certain value of ?a, and a different one 5 minutes later.

We'd name it something which reflects this fact, such as "transaction_start_time();"

That would work for us and allows for Now() (not transaction-bound) in the future, if needed.