From: | joseppi c <joseppic(at)yahoo(dot)co(dot)uk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Date ranges + DOW select question |
Date: | 2006-06-15 10:14:12 |
Message-ID: | 20060615101412.3443.qmail@web25413.mail.ukl.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi,
I have a table which contains starttime, endtime and
DOW; i.e. a weekly list of times for when a process
must be started and ended.
TABLE: cronTimes
FIELDS: starttime, endtime, dayOfWeek
I have another table which contains date ranges.
TABLE: dateRanges
FIELDS: dateStart, dateEnd
I need to get a list of cronTimes records for a record
in dateRanges and push these to a temporary table.
i.e. tell me which dates are affected by cronTimes.
I have got a little way on this but need some
assistance.
SELECT * FROM cronTimes WHERE
starttime >= '00:00:00' AND endtime <= '23:59:59'
AND dayOfWeek >= (EXTRACT(DOW FROM TIMESTAMP
'2006-06-26')) AND dayOfWeek <= (EXTRACT(DOW FROM
TIMESTAMP '2006-07-04'));
The problem with the above is that by casting the
dateStart and dateEnd they become numbers between 0
and
6 which inturn invalidates the < & > as they are
nolonger working on dates, nor a sequence as numbers
can be repeated.
Do I need to generate a sequence of dates somehow so
that each date in the range can be compared to the
cronTimes table (so I can use the 'IN' condition)?
Am I in the realms of plpgsql?
Any advice on the above welcome.
Joseppic.
Send instant messages to your online friends http://uk.messenger.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Milen Kulev | 2006-06-15 11:01:56 | UTF-8 Problem ? |
Previous Message | Christopher Browne | 2006-06-14 20:55:14 | Re: Good examples of calling slony stored procedures |