sql - In oracle, how do I create a check constraint that checks if the time is between 10:00:00 and 21:00:00?

Question

In an Oracle database, I have a registration date column. Using SQL, I want to add a check constraint that checks the time of the date is between 10:00:00 and 21:00:00. How can I do this?

question from:https://stackoverflow.com/questions/65889955/in-oracle-how-do-i-create-a-check-constraint-that-checks-if-the-time-is-between

Answer 1

Ideally, the constraint should use date-time (and interval) expressions and functions as much as possible. Alas, in Oracle date arithmetic works mostly with numbers (representing "days"), so you do need to convert somewhere.

I would do it like this - easiest (in my opinion) for future developers to read and understand:

check (numtodsinterval(reg_date - trunc(reg_date), 'day') 
           between interval '10' hour and interval '21' hour)

trunc(reg_date) returns the date, with the time-of-day truncated to midnight. Then reg_date - trunc(reg_date) returns the time-of-day stored in reg_date - but the result is a number (expressed in days). I convert this to an interval with the numtodsinterval, specifying that the number represents "days". The rest should be obvious.

By the way, if you must check that the time-of-day is, for example, between 10:20 and 21:20, you can use the same approach - use interval 10:20 hour to minute, for example, in the between condition. The advantage (in readability) of writing the condition in this way becomes even clearer in those cases.