pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate ranges + DOW select question
2+ messages / 2 participants
[nested] [flat]
* Date ranges + DOW select question
@ 2006-06-15 10:14 joseppi c <joseppic@yahoo.co.uk>
0 siblings, 1 reply; 2+ messages in thread
From: joseppi c @ 2006-06-15 10:14 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Date ranges + DOW select question
@ 2006-06-22 15:25 Aaron Bono <postgresql@aranya.com>
parent: joseppi c <joseppic@yahoo.co.uk>
0 siblings, 0 replies; 2+ messages in thread
From: Aaron Bono @ 2006-06-22 15:25 UTC (permalink / raw)
To: joseppi c <joseppic@yahoo.co.uk>; +Cc: 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-06-22 15:25 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-15 10:14 Date ranges + DOW select question joseppi c <joseppic@yahoo.co.uk>
2006-06-22 15:25 ` Aaron Bono <postgresql@aranya.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox