By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,835 Members | 1,024 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,835 IT Pros & Developers. It's quick & easy.

constraint help

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2

P: n/a

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.