Message-ID: <42676D26.2060506@cerene.fr>
Date: Thu, 21 Apr 2005 11:06:46 +0200
From: =?ISO-8859-1?Q?St=E9phane_RIFF?= <stephane.riff@cerene.fr>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.7.5) Gecko/20041217
MIME-Version: 1.0
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query question
References: <42667F3C.6080303@cerene.fr>
 <426692FC.6020807@akyasociados.com.ar>
In-Reply-To: <426692FC.6020807@akyasociados.com.ar>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

I do some tests with your first query and it seems to works.
Thanks a lot for your answer, i will post the final thought later
Thanks again
bye

Franco Bruno Borghesi wrote:

> If you have a row every 15 seconds, the answer is quite easy:
>
>SELECT
>	A1.date
>FROM
>	activity A1
>	LEFT JOIN activity A2 ON (A2.date=A1.date-'15 secs'::interval)
>WHERE
>	A1.state<>A2.state OR A2.state IS NULL
>ORDER BY 1
>  
>
>
>
> Now if you don't have a row every 15 seconds, the answer is a bit more 
> complex (at least I couldn't think of an easier solution):
>
>SELECT
>	min(TMP2.new_date)
>FROM
>	(
>		SELECT
>			DISTINCT
>			TMP.new_date,
>			max(TMP.old_date) AS max_old_date
>		FROM
>			(
>				SELECT	
>					A1.id AS new_id, A1.date AS new_date, A1.state AS new_state,
>					A2.id AS old_id, A2.date AS old_date, A2.state AS old_state
>				FROM
>					activity A1
>					LEFT JOIN activity A2 ON (A2.date<A1.date)
>				ORDER BY
>					A1.date, A2.date DESC	
>			) AS TMP
>		WHERE
>			TMP.old_state<>TMP.new_state OR TMP.old_state IS NULL
>		GROUP BY
>			TMP.new_date
>	) TMP2
>GROUP BY
>	TMP2.max_old_date
>ORDER BY 1
>  
>
>
> I've tested both queries on postgreSQL 8 with the data you provided, 
> and they both work. Anyway try them with larger datasets before using 
> them in real life ;-)
>
> Hope it helps.
>
>
> Stéphane RIFF wrote:
>
>> Hi ,
>>
>> I have table that represent a switch activity like this :
>>
>> |         date                    | state  |
>> | 2005-04-20 17:00:00 |   0     |
>> | 2005-04-20 17:00:15 |   0     |
>> | 2005-04-20 17:00:30 |   1     |
>> | 2005-04-20 17:00:45 |   1     |
>> | 2005-04-20 17:01:00 |   1     |
>> | 2005-04-20 17:01:15 |   0     |
>> | 2005-04-20 17:01:30 |   0     |
>> | 2005-04-20 17:01:45 |   0     |
>>
>> I want to get the date of each states change but i not a sql expert.
>> Can someone advices me
>>
>> Thanks
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 7: don't forget to increase your free space map settings
>>
>
>------------------------------------------------------------------------
>
>No virus found in this incoming message.
>Checked by AVG Anti-Virus.
>Version: 7.0.308 / Virus Database: 266.10.1 - Release Date: 20/04/2005
>  
>