Temporal tables

[LostKobrakai]

I‘m wondering if there are any plans on implementing support for (bi-)temporal data, so one can look at the db state from a certain point in time and/or have data be valid only for a set amount of time.

[1st1]

Yes, we'll likely add support for temp object types, but likely after 1.0.

[1st1]

Or perhaps you meant a different feature: make it possible for data to have some sort of "expiry date" set for it, with the DB cleaning it up automatically. We'll also have support for access control soon, which is really a general purpose query rewrite. Perhaps you'll be able to emulate what you need with triggers and query rewrite. But it's unlikely that we'll make "temporal data" a first-class feature anytime soon.

[LostKobrakai]

I meant it in the way described in e.g. https://en.wikipedia.org/wiki/SQL:2011

[1st1]

Once Postgres adds native support we'll consider adding it too.

[Lisser]

Hello!

I have similar interest in temporal data as @LostKobrakai. For a project that I am working on, we are extremely interested in looking in the past state of the data.

If there is no built-in support in EdgeDB, would it possible (and relatively straightforward) to include temporality in the model? E.g. I am thinking of adding a start and end valid-time for each object.

[obeavers]

@elprans @Lisser how did it go? Have a good window to probably migrate a young-ish platform to EdgeDB but this is pretty important to have hashed out before committing.

[agritheory]

@Lisser Would love to hear your experiences on this, however far you progressed with this idea.

[Lisser]

@obeavers @agritheory

I am so sorry, but I didn't yet get to it unfortunately.. Too busy rolling out features to keep our project alive 😅

[obeavers]

By the way, the proposed query rewrite mechanism will make this a lot simpler, because the access rules essentially turn every type into an alias, so you'd have a lot simpler schema:

abstract type Temporal {
  required property valid_from -> datetime;
  required property valid_to -> datetime;

  access policy temporal permit all using (
    .valid_from <= datetime_of_transaction() 
    and .valid_to >= datetime_of_transaction()
  )
}

And then all objects extending from Temporal will be magically filtered based on current time.

Does the v2 release address this?

[elprans]

Does the v2 release address this?

Yes.