Calculating quantity of available resource from reservations database table

I am working on a project where i need to store reservations of a certain resource. Before inserting a new reservation, it has to be checked whether, in the time interval specified, there is at least the quantity of resource asked. Reservations are stored in a relational database with the following schema:

Here is a visual representation of the aforementioned data set.

I have two questions:

1. How could I calculate the maximum quantity of available resource from 2023/01/15-00:00 and 2023/01/15:23:59? It should be an extension of finding all the reservations that overlap with them other, and progressively calculating the sum of their associated resource quantity.

2. Do you think this is the proper way to handle this sort of data? Perhaps I should consider using another temporal model?

CodePudding user response：

Given an interval @interval_start and @interval_end (both are timestamps) the overlapping rows will be found with:

SELECT *
FROM <your table>
WHERE
timestamp_start <= @interval_end AND timestamp_end >= @interval_start

Every time there's a "start" you must add resources, and every time there's an "end" you must subtract resources. For this reason you must have a sequence of events like this:

SELECT
timestamp_start as event_timestamp,
resource_quantity

FROM <your table>
WHERE
timestamp_start <= @interval_end AND timestamp_end >= @interval_start

UNION ALL

select
timestamp_end as event_timestamp,
(-1) * resource_quantity as resource_quantity
FROM <your table>
WHERE
timestamp_start <= @interval_end AND timestamp_end >= @interval_start

ORDER BY event_timestamp

At this point you write a procedural SQL which scrolls down this table's rows and, starting from 0, adds the resource_quantity and keeps the maximum value found if the event_timestamp is inside the given interval.

If you need I can help you write the procedure