Re: Date ranges + DOW select question - Mailing list pgsql-sql

From Aaron Bono
Subject Re: Date ranges + DOW select question
Date
Msg-id bf05e51c0606220825v29e4319w8736c3183cfbdb5d@mail.gmail.com
Whole thread Raw
In response to Date ranges + DOW select question  (joseppi c <joseppic@yahoo.co.uk>)
List pgsql-sql
I am a little confused.  Where are you casting dateStart and dateEnd?  I don't see either in your query.  I assume dayOfWeek is a number between 0 and 6, or maybe not?

A little more detail would help.

Thanks,
Aaron Bono

On 6/15/06, joseppi c <joseppic@yahoo.co.uk> wrote:
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.

pgsql-sql by date:

Previous
From: "Forums @ Existanze"
Date:
Subject: Re: Start up question about triggers
Next
From: "Aaron Bono"
Date:
Subject: Re: join on next row