Re: Query question

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
>