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)
)
)
)