Query for time intervals

[Primus]

Hi.

I would like to learn how to create queries with time type columns.

I’m attaching an ultra-simplified database containing just one table (with ~ 100 rows):

CREATE TABLE T_SLOTS (
SLO_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
OPE_ID INTEGER NOT NULL, -- operator
RES_ID INTEGER NOT NULL, -- resident
DAY_ID TINYINT NOT NULL, -- from 1 (monday) to 7 (sunday)
HOU_BEG TIME NOT NULL, -- hourly begin
HOU_END TIME NOT NULL, -- hourly end
CONSTRAINT PK_SLO_ID PRIMARY KEY (SLO_ID)
);

Operators work for residents. In the complete database, I have an acurate overview of each schedule, but what I’d like is to obtain the available time slots, for example, between 08:00:00 and 18:00:00, for a given operator, according to the slots already filled with, in addition, possibly, time slots where the operator doesn’t want to work.

Example:

operator 1 works the monday :

• at resident 1 from 08:00 to 10:00
• at resident 2 from 10:00 to 12:00
• at resident 3 from 15:00 to 17:00

The result of the query would therefore be from 12:00 to 15:00 and from 17:00 to 18:00.
But taking into account that the operator doesn’t want to work between 12:00 and 13:30 → the result would be from 13:30 to 15:00 and from 17:00 to 18:00.

I think you should use INTERVAL and/or EXCEPT, but I’ve never used them before.

Thanks.

dbPrimus20250625.odb (6,1 Ko)

[Villeroy]

Select the whole table with 101 records plus the earliest begin for same day and same operator of the same table.

SELECT "T1"."SLO_ID", "T1"."OPE_ID", "T1"."RES_ID", "T1"."DAY_ID", "T1"."HOU_BEG", "T1"."HOU_END", MIN( "T2"."HOU_BEG" ) AS "NEXT" 
FROM "T_SLOTS" AS "T1" LEFT JOIN "T_SLOTS" AS "T2" 
  ON "T1"."DAY_ID" = "T2"."DAY_ID" 
  AND "T1"."OPE_ID" = "T2"."OPE_ID" 
  AND "T1"."HOU_BEG" < "T2"."HOU_BEG"
GROUP BY "T1"."SLO_ID", "T2"."OPE_ID", "T1"."RES_ID", "T1"."DAY_ID", "T1"."HOU_BEG", "T1"."HOU_END"

[Primus]

Thanks, but I don’t quite understand. The following screenshot is the schedule for operator OPE_ID = 1.

For example, for mardi (tuesday) the schedule shows 8:00 to 10:00 and 10:00 to 12:00. What I’m looking for is for the query to indicate the free hours between 8:00 and 18:00, i.e. for this example → 12:00 to 18:00.

But I’d also like to be able to indicate a time slot during which the operator isn’t working; for example, between 12:00 and 13:30. In this case, the hours available for this day would be from 13:30 to 18:00.

But it seems to me that to achieve this, you need to add a reference table for the hours.

[RobertG]

Might be this helps a little bit:

SELECT "b"."OPE_ID", "b"."DAY_ID", "b"."HOU_END" , "b"."FREE_END" 
FROM (
SELECT "a".*, 
   (
   SELECT COALESCE(MIN("HOU_BEG"),CAST('18:00:00' AS TIME)) 
   FROM "T_SLOTS" 
   WHERE "OPE_ID" = "a"."OPE_ID" AND "DAY_ID" = "a"."DAY_ID" 
      AND "HOU_BEG" >= "a"."HOU_END"
   ) AS "FREE_END" 
FROM "T_SLOTS" AS "a"
) AS "b" 
WHERE "b"."FREE_END" > "b"."HOU_END" ORDER BY "OPE_ID", "DAY_ID", "HOU_BEG"

Won’t show the right result from 12:00 to 13:30, but will show all empty intervals.