agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Stéphane RIFF <stephane.riff@cerene.fr>
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: Query question
Date: Thu, 21 Apr 2005 11:06:46 +0200
Message-ID: <42676D26.2060506@cerene.fr> (raw)
In-Reply-To: <426692FC.6020807@akyasociados.com.ar>
References: <42667F3C.6080303@cerene.fr>
<426692FC.6020807@akyasociados.com.ar>
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
>
>
view thread (15+ messages) latest in thread
Message-ID: <42676D26.2060506@cerene.fr>
Permalink: ../42676D26.2060506@cerene.fr/
Also on: postgresql.org/message-id/42676D26.2060506@cerene.fr
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: stephane.riff@cerene.fr, franco@akyasociados.com.ar
Subject: Re: Query question
In-Reply-To: <42676D26.2060506@cerene.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox