maria-discuss team mailing list archive

Thread
Date

How to select values at particular time with two temporal tables in MariaDB?

To: "maria-discuss@xxxxxxxxxxxxxxxxxxx" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
From: unit unit <uunit@xxxxxxxxxxx>
Date: Tue, 26 May 2020 04:52:29 +0000
Accept-language: en-US
Arc-authentication-results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none
Arc-message-signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=mI48lK2ux5JCYelhpIq1kSguR3NxVOYpr0+jDoZKpUc=; b=mfPm3rbSnI98bB73A+OpB4avNfkY2bT1e4ZUxIs6b8G+dX/Hcg5vY8M1e4Y2b8jf3yg/EOClrXY1g9saMNwKicr7Qz5HaCJjk2BEeL14mYIaoAFfkgalrGE+0InNj46SzkkN8Ti7Eb+ltkFd4mTfyd59khJjF/TYuBKvQdXYYBZZrxS14djo/criZ7JHCQItTk59evccR/3jL0/Bq+sVr5ix+o/NR65Iv7kOdBqA+ULh0O2N7zBmeeZjn+zcsgJQ0sG0cCQWMRgOYI3ikFrzCDoCjql/SM8mA3eIEpsO0ifTYjGPxU2oRfj8jjinJsqAv+qm3ZJC4LhQN8FPcIAHOw==
Arc-seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=FmQULZOo4bnurvhZvtaKFjHS875mbAC8Wy9zKJ/OQSyOiSx6t4KiyPDQs40rABJmCtCJMYP/ZOMSpECs/iqMOy7bp8oBw9eLQxlHSiZ/TKyRVVXXXFIjnFiI6rbs06iYTvJcvBAjW1+DKhqTqph9HgA0gvOHPzqpwY+OpojF3sLcXbXPXUUU+BlV4C83OnzYl7IqdOvQgDCxa2t9yGxvZD6sQWzLzHAlxhqnvxk+R0PXvK2aBpwieDfuuAJl5JXtA0pgPzbdjLX0dlnd52ZRjlHojqKXYFMnZ71to5HmOoqgFbq6RLXg98dv+mmxF87/fWKCRJZEwBbjG8E5lZoGOA==
Thread-index: AQHWMxi7Wi3WMxMD9UOygHzePj1fCg==
Thread-topic: How to select values at particular time with two temporal tables in MariaDB?

Hi,

I'm interested in using MariaDB for temporal data but I'm a bit uncertain on how to solve certain use cases. As an example let's say I define two temporal tables<https://mariadb.com/kb/en/temporal-data-tables> using application time periods<https://mariadb.com/kb/en/temporal-data-tables/#application-time-periods> where one table has a one to many relationship with the other. For example:

[cid:8f4d19a2-4743-4e32-9516-16a626f9169f]

What I'm looking for is a way to "select a teacher and count the number of associated classes for this teacher" at a moment in time. My questions are:

 1. How would I define/create the two tables using temporal tables?
 2. What would such a select statement look like?
 3. What indexes should be created in order to make the select statement efficient?

Note that it's very important that the query is expressed using application time and not system time.

BR,
/Johan