Thread: constraint help

I will have a table with, among other things, two time columns.
As per my last post, I will be using a variable time for the day-to-day
boundary, versus the traditional 12pm.
What I want to be able to do upon insert of a record with a pair of
times is to determine if the day to day boundary is between the times
that are in the record, and reject the record if that is true.
For example, if the system wide constant for the day-to-day boundary
were 4AM local time, and I submitted a record like so:
    INSERT INTO my_table(start_time, end_time) VALUES( '12:00'::TIME,
'14:30'::TIME );

that should succeed. However:
    INSERT INTO my_table(start_time, end_time) VALUES( '12:00'::TIME,
'5:00'::TIME );

should fail.
Any ideas wold be most appreciated.

Maybe natural join is what you want?

On Wed, 14 Apr 2004, Dennis Gearon wrote:

> I will have a table with, among other things, two time columns.
> As per my last post, I will be using a variable time for the day-to-day
> boundary, versus the traditional 12pm.
> What I want to be able to do upon insert of a record with a pair of
> times is to determine if the day to day boundary is between the times
> that are in the record, and reject the record if that is true.
> For example, if the system wide constant for the day-to-day boundary
> were 4AM local time, and I submitted a record like so:
>     INSERT INTO my_table(start_time, end_time) VALUES( '12:00'::TIME,
> '14:30'::TIME );
>
> that should succeed. However:
>     INSERT INTO my_table(start_time, end_time) VALUES( '12:00'::TIME,
> '5:00'::TIME );
>
> should fail.
> Any ideas wold be most appreciated.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

On Wed, 14 Apr 2004, Dennis Gearon wrote:

> I will have a table with, among other things, two time columns.
> As per my last post, I will be using a variable time for the day-to-day
> boundary, versus the traditional 12pm.
> What I want to be able to do upon insert of a record with a pair of
> times is to determine if the day to day boundary is between the times
> that are in the record, and reject the record if that is true.
> For example, if the system wide constant for the day-to-day boundary
> were 4AM local time, and I submitted a record like so:
>     INSERT INTO my_table(start_time, end_time) VALUES( '12:00'::TIME,
> '14:30'::TIME );
>
> that should succeed. However:
>     INSERT INTO my_table(start_time, end_time) VALUES( '12:00'::TIME,
> '5:00'::TIME );
>
> should fail.

Hmm, well as a constant and assuming the boundary is considered on the
"next" day and the same time twice as being 24 hours apart, I think it'd
be something like the bit that follows with replacing the constant as
appropriate (untested):

 (
  (
   start_time < end_time AND
   (
    (start_time < '4:00'::time AND end_time < '4:00'::time) OR
    (start_time >= '4:00'::time)
   )
  ) OR
  (
   start_time > end_time AND
   (
    (start_time >='4:00'::time AND end_time < '4:00'::time) OR
    (start_time < '4:00'::time)
   )
  )
 )