Oracle SQL: check if time is between two instances (excluding dd/mm/yyyy)

Question

I am building an Oracle APEX application in which journalists can bring in articles, which a director selects for a broadcast, along with a newsreader (who can read the articles if the broadcast is created). I am kind of stuck with the following:
I would like to create an Oracle SQL query which selects the first and last name of a record in the "user" table, when its availability (which is stored in another table, "Beschikbaarheid") meets the given circumstances (user begin time at broadcast day >= broadcast begin time AND user end time at broadcast day <= broadcast end time). I myself have written the following query.

SELECT voornaam || ' ' || achternaam AS display_value, id AS return_value
FROM Gebruiker
WHERE id NOT IN (SELECT Nieuwslezerselectie.nieuwslezerid FROM Nieuwslezerselectie 
WHERE -- other conditions
AND id IN (SELECT gebruikerid 
           FROM Beschikbaarheid 
           WHERE (dag = to_char(to_date(:P91_DATUM,'DD-MON-YYYY'),'Day'))
           AND (to_date(:P91_BEGINTIJD,'HH24:MI') >= to_date(begintijd,'HH24:MI'))
           AND (to_date(:P91_EINDTIJD,'HH24:MI') <= to_date(eindtijd,'HH24:MI'))
          );

(Excuse me if you are having trouble reading this, I use Dutch naming. Feel free to ask if something is unclear.)

However, the APEX select list to which this query is linked is not showing anything if the newsreader is available at the time of the broadcast. I think the problem is "Begintijd" and "Eindtijd" are stored as time stamps, as the values should be date-independent (which time stamps are not), but I have no clue how to store them otherwise. As a varchar maybe?

I sincerely hope you have any ideas. Thank you in advance!

Luc

update 1

With help I have rewritten the query, it now looks like this:

AND id IN (SELECT gebruikerid 
       FROM Beschikbaarheid
       WHERE (dag = to_char(to_date(:P91_DATUM,'DD-MON-YYYY'),'Day','NLS_DATE_LANGUAGE=Dutch'))
       AND (
            to_date('01-01-2000' || ' ' || to_char(to_timestamp(:P91_BEGINTIJD),'HH24:MI'),'DD-MM-YYYY HH24:MI') 
            BETWEEN
            to_date('01-01-2000' || ' ' || to_char(begintijd,'HH24:MI'),'DD-MM-YYYY HH24:MI')
            AND
            to_date('01-01-2000' || ' ' || to_char(eindtijd,'HH24:MI'),'DD-MM-YYYY HH24:MI')
           )
       AND (
            to_date('01-01-2000' || ' ' || to_char(to_timestamp(:P91_EINDTIJD),'HH24:MI'),'DD-MM-YYYY HH24:MI') 
            BETWEEN 
            to_date('01-01-2000' || ' ' || to_char(begintijd,'HH24:MI'),'DD-MM-YYYY HH24:MI')
            AND
            to_date('01-01-2000' || ' ' || to_char(eindtijd,'HH24:MI'),'DD-MM-YYYY HH24:MI')
           )
      );

However, I am now getting the ORA-01840: input value not long enough for date format error when trying to access the page. I haven't discovered what causes that error yet. Does anyone have any clues?

update 2

Now I am confused. It turned out I didn't need to_char etc. for my page variables, so I removed those (to_char etc.). Now I am normally send to the correct page, but the last value I entered in the "set these items / with these values" text fields on the previous page, isn't send. This is what I have in these text fields right now:

In this image, P85_BEGINTIJD isn't send. I also switched the values around, but that didn't make a difference. Is there a limit to the amount of values you can send with a request, or something?

Answer 1

Do not use timestamps. se a date field, which includes date and time, more info about date fields in oracle: Oracle Data types, see paragraph Overview of DATE Datatype

Answer 2

If I got it right you have two columns with data type TIMESTAMP and you need to constraint it with a parameter, but using only the hour an minute part of it.

The simplest possible way to do it is use a string parameter in format HH:MI

 select begintijd, to_char(begintijd,'HH24:MI') begintijd_hhmi, 
 eindtijd, to_char(eindtijd,'HH24:MI') eindtijd_hhmi from tst
 where '22:00' between to_char(begintijd,'HH24:MI')  and to_char(eindtijd,'HH24:MI')
 ;

Note that you use to_char with the date mask HH24:MI to get rid of the not relevant part of the date/time.

A probable better and more flexible way is to use INTERVALs.

 with t2 as (
  select 
  begintijd, begintijd - trunc(begintijd) begintijd_hhmi, 
  eindtijd, eindtijd - trunc(eindtijd) eindtijd_hhmi from tst)
 select * from t2
 where INTERVAL '22:00' HOUR TO MINUTE between begintijd_hhmi  and  eindtijd_hhmi
 ;

You use the difference of the timestamp and the truncated timestamp to calculate the interval (of hour and minutes). Note though, that if the timestamp can contain seconds or fractions of it, you may need to trim them out, e.g. with

 NumToDsInterVal(60*extract(hour from begintijd)+extract(minute from begintijd),'MINUTE') begintijd_hhmi

Answer 3

Okay, so eventually, I was able to solve it. In my last update, the only lasting problem was that some variables weren't send to the next page. I wasn't able to find the root cause of the problem, so I did a workaround.

The only variable that was sent normally to the next page is P91_UITZENDING (the PK of Uitzending). Luckily, the other variables I needed (begintijd, eindtijd and datum) are attributes of Uitzending so I was able to derive them with statements like this in the source of the corresponding page item:

SELECT to_char(begintijd,'HH24:MI')
-- replace with eindtijd for eindtijd, and for date: to_char(datum,'DD-MON-YYYY')
FROM Uitzending
WHERE id = :P91_UITZENDING

And the final SQL query for the select box:

SELECT voornaam || ' ' || achternaam AS display_value, id AS return_value
FROM Gebruiker
WHERE -- Other statements
AND id IN (SELECT gebruikerid 
           FROM Beschikbaarheid
           WHERE (dag = to_char(to_date(:P91_DATUM,'DD-MON-YYYY'),'fmDay','NLS_DATE_LANGUAGE=Dutch'))
           AND (
                to_date('01-01-2000' || :P91_BEGINTIJD,'DD-MM-YYYY HH24:MI') 
                BETWEEN
                to_date('01-01-2000' || to_char(begintijd,'HH24:MI'),'DD-MM-YYYY HH24:MI')
                AND
                to_date('01-01-2000' || to_char(eindtijd,'HH24:MI'),'DD-MM-YYYY HH24:MI')
               )
           AND (
                to_date('01-01-2000' || :P91_EINDTIJD,'DD-MM-YYYY HH24:MI') 
                BETWEEN 
                to_date('01-01-2000' || to_char(begintijd,'HH24:MI'),'DD-MM-YYYY HH24:MI')
                AND
                to_date('01-01-2000' || to_char(eindtijd,'HH24:MI'),'DD-MM-YYYY HH24:MI')
               )
          );

I still don't know why begintijd, eindtijd and datum weren't send the normal way, but at least it is working now.

Thank you all for your thinking and help!