DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=gXtFb1MPCDhE+b3NkBYmWUOKOD7AivvbfqmKDggJeolHBzIuALG6ePVuw+cYTFt6Wqp9JomqX0FZMz3ZidS+3TNcDkqDt+zqSRIk8yaRKvcYGMsARRkyXhzvYJZcDqZ7QNpC/V8FjwjYFAq8IZxpf3gR0cWaaFvYb3UIhmYpN7Q=
	;
Message-ID: <20060615101412.3443.qmail@web25413.mail.ukl.yahoo.com>
Date: Thu, 15 Jun 2006 11:14:12 +0100 (BST)
From: joseppi c <joseppic@yahoo.co.uk>
Subject: Date ranges + DOW select question
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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