Time Series Data - find times of state transition
Single time series
SELECT tsFROM ( SELECT *, lag(state) OVER (ORDER BY ts) AS last_state FROM tbl ) subWHERE state = 'B'AND last_state = 'A'ORDER BY ts; -- I assume you want ordered results@Denis already provided some links.
Multiple time series
... identified by a timeseries_id (answer to comment):
SELECT timeseries_id, tsFROM ( SELECT *, lag(state) OVER (PARTITION BY timeseries_id ORDER BY ts) AS last_state FROM tbl ) subWHERE state = 'B'AND last_state = 'A'ORDER BY timeseries_id, ts;
You need to use the lead or lag window function:
http://www.postgresql.org/docs/current/static/tutorial-window.html
http://www.postgresql.org/docs/current/static/functions-window.html