From: | "Aaron Bono" <postgresql(at)aranya(dot)com> |
---|---|
To: | "joseppi c" <joseppic(at)yahoo(dot)co(dot)uk> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Date ranges + DOW select question |
Date: | 2006-06-22 15:25:06 |
Message-ID: | bf05e51c0606220825v29e4319w8736c3183cfbdb5d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)yahoo(dot)co(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Aaron Bono | 2006-06-22 15:43:57 | Re: join on next row |
Previous Message | Richard Broersma Jr | 2006-06-22 14:41:41 | Re: Doubt in stored procedure |