Query for time intervals

English
#1

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)

#2

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"
#3

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

screenshot

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.

#4

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.

Tested a little bit more: Seems there are intervals, which break the rule to have a break from 12:00 up to 13:30. So the break should be defined at another place. I have create a view for a combination of existing table and all breaks, then started the query and get the free hours. But it won’t be the right result when there are “wrong” entries for HOU_BEG or HOU_END
dbPrimus20250625.odb (7.2 KB)

#5

Already, thanks for the work, and it’s true that some rows have been entered without paying too much attention at the 12:00 - 13:30 slot, which shouldn’t be taken into account.

But this line is still problematic:

SELECT COALESCE(MIN(“HOU_BEG”),CAST(‘18:00:00’ AS TIME))

Instead of HOU_BEG, can we define ‘08:00:00’? Because if, for a given day, the operator starts at 10:00, for example, availability from 08:00 to 10:00 is not indicated.

#6

@Primus : The line you marked isn’t the problematic line. If I won’t set this the end of the day won’t appear. Then the last entry of the day will define the last possible interval of the day. Will have a look how to get the first possible entry.

Added a start time in the view:
dbPrimus20250625.odb (7.3 KB)

#7

Well, I wouldn’t have thought it would be so complicated. But a question: in the “viw_with_break” view, why are there lines from 07:59:00 to 08:00:00?

#8

The query could only calculate free interval if there is a start of the interval (first interval of a day). I have set the first interval of every day from 7:59 to 8:00, because the query needs a difference between “HOU_BEG” AND “HOU_END”.

#9

My query returns the full schedule plus the start time of the next appointment of the same operator at the same day.
If the next time is Null, then the operator has no more appointment at that day.
Datediff can calculate the differences between the end time and the next time. A negative Datediff indicates overlapping time intervals.
dbPrimus20250626_v.odb (18.1 KB)

#10

Now I understand. Thanks again. :slightly_smiling_face:

#11

Thanks too, I’ll take a look and make some comparisons with the base I’ve created.

#12

Here’s what it looks like with the following time slots:

  • From 8:00 to 12:00
  • From 13:00 to 19:00

screenshot
screenshot772×576 79.6 KB

So visually you have a perfectly clear view of the free slots (it could be even more complicated with customized slots for each operator).

Thank you both… :slightly_smiling_face:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.