X-Original-To: pgsql-sql-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id A01C59FA606
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 22 Jun 2006 12:25:16 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 94281-07 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 22 Jun 2006 12:25:09 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.200])
	by postgresql.org (Postfix) with ESMTP id 11C1B9F9FE3
	for <pgsql-sql@postgresql.org>; Thu, 22 Jun 2006 12:25:07 -0300 (ADT)
Received: by wx-out-0102.google.com with SMTP id s13so331461wxc
	for <pgsql-sql@postgresql.org>; Thu, 22 Jun 2006 08:25:07 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
	b=gFLouNpvKm+HdbHKX4Yoa/1QaGbCZGxDAqqzrHmKk90jN5B4D90D/5fvJjmKCSjcifkRQy5LRx8nkYjRk9SJKmzoDgkaYSgsyysql/BsNi1fciTGg3TfnvhMuAimHrTTTdsREYyQ3bRHsSzIVJCGrpl/KnfKA/G5KSViECYvLls=
Received: by 10.70.99.19 with SMTP id w19mr3276239wxb;
	Thu, 22 Jun 2006 08:25:07 -0700 (PDT)
Received: by 10.70.66.15 with HTTP; Thu, 22 Jun 2006 08:25:06 -0700 (PDT)
Message-ID: <bf05e51c0606220825v29e4319w8736c3183cfbdb5d@mail.gmail.com>
Date: Thu, 22 Jun 2006 10:25:06 -0500
From: "Aaron Bono" <postgresql@aranya.com>
To: "joseppi c" <joseppic@yahoo.co.uk>
Subject: Re: Date ranges + DOW select question
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20060615101412.3443.qmail@web25413.mail.ukl.yahoo.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_9620_18926352.1150989906951"
References: <20060615101412.3443.qmail@web25413.mail.ukl.yahoo.com>
X-Google-Sender-Auth: 8fb229e8b2a717d5
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200606/250
X-Sequence-Number: 25486

------=_Part_9620_18926352.1150989906951
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.

------=_Part_9620_18926352.1150989906951
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

I am a little confused.&nbsp; Where are you casting dateStart and dateEnd?&nbsp; I don't see either in your query.&nbsp; I assume dayOfWeek is a number between 0 and 6, or maybe not?<br><br>A little more detail would help.<br><br>Thanks,
<br>Aaron Bono<br><br><div><span class="gmail_quote">On 6/15/06, <b class="gmail_sendername">joseppi c</b> &lt;<a href="mailto:joseppic@yahoo.co.uk">joseppic@yahoo.co.uk</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi,<br>I have a table which contains starttime, endtime and<br>DOW; i.e. a weekly list of times for when a process<br>must be started and ended.<br><br>TABLE: cronTimes<br>FIELDS: starttime, endtime, dayOfWeek<br><br>I have another table which contains date ranges.
<br><br>TABLE: dateRanges<br>FIELDS: dateStart, dateEnd<br><br>I need to get a list of cronTimes records for a record<br>in dateRanges and push these to a temporary table.<br>i.e. tell me which dates are affected by cronTimes.
<br><br>I have got a little way on this but need some<br>assistance.<br><br>SELECT * FROM cronTimes WHERE<br>starttime &gt;= '00:00:00' AND endtime &lt;= '23:59:59'<br>AND dayOfWeek &gt;= (EXTRACT(DOW FROM TIMESTAMP<br>'2006-06-26')) AND dayOfWeek &lt;=&nbsp;&nbsp;(EXTRACT(DOW FROM
<br>TIMESTAMP '2006-07-04'));<br><br>The problem with the above is that by casting the<br>dateStart and dateEnd they become numbers between 0<br>and<br>6 which inturn invalidates the &lt; &amp; &gt; as they are<br>nolonger working on dates, nor a sequence as numbers
<br>can be repeated.<br><br>Do I need to generate a sequence of dates somehow so<br>that each date in the range can be compared to the<br>cronTimes table (so I can use the 'IN' condition)?<br><br>Am I in the realms of plpgsql?
<br><br>Any advice on the above welcome.<br><br>Joseppic.</blockquote></div>

------=_Part_9620_18926352.1150989906951--