X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BCE7C54036 for ; Wed, 20 Apr 2005 14:36:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88110-03 for ; Wed, 20 Apr 2005 17:36:13 +0000 (GMT) Received: from lucifer.oficina (unknown [200.68.121.201]) by svr1.postgresql.org (Postfix) with ESMTP id AB6C154030 for ; Wed, 20 Apr 2005 14:36:10 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by lucifer.oficina (8.13.1/8.12.10) with ESMTP id j3KHa9it040398; Wed, 20 Apr 2005 14:36:09 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Received: from lucifer.oficina ([127.0.0.1]) by localhost (lucifer.oficina [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 37596-28; Wed, 20 Apr 2005 14:36:00 -0300 (ART) Received: from [192.168.1.168] (taz.oficina [192.168.1.168]) (authenticated bits=0) by lucifer.oficina (8.13.1/8.12.10) with ESMTP id j3KHZvE0040379; Wed, 20 Apr 2005 14:35:57 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Message-ID: <426692FC.6020807@akyasociados.com.ar> Date: Wed, 20 Apr 2005 14:35:56 -0300 From: Franco Bruno Borghesi User-Agent: Mozilla Thunderbird 1.0 (X11/20041216) X-Accept-Language: en-us, en MIME-Version: 1.0 To: =?ISO-8859-1?Q?St=E9phane_RIFF?= Cc: pgsql-sql@postgresql.org Subject: Re: Query question References: <42667F3C.6080303@cerene.fr> In-Reply-To: <42667F3C.6080303@cerene.fr> Content-Type: multipart/alternative; boundary="------------080102060309030106080009" X-Virus-Scanned: by amavisd-new at akyasociados.com.ar X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.253 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_40_50, HTML_MESSAGE, HTML_TITLE_EMPTY, RCVD_IN_SORBS_DUL X-Spam-Level: X-Archive-Number: 200504/306 X-Sequence-Number: 21199 This is a multi-part message in MIME format. --------------080102060309030106080009 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit 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.dateTMP.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 > --------------080102060309030106080009 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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


--------------080102060309030106080009--