Nicoletta Maia, 14.09.2011 10:30:
> SELECT `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp` ,
> MIN( `Y`.`history_timestamp` ) AS `start_time`
> FROM
> `Table` AS `X`
> JOIN
> `Table` AS `Y`
> ON `X`.`consumer_id` = `Y`.`consumer_id`
> AND `X`.`move_date` = `Y`.`move_date`
> AND `X`.`history_timestamp`>= `Y`.`history_timestamp`
> WHERE NOT EXISTS (
> SELECT *
> FROM `Table` AS `Z`
> WHERE `X`.`consumer_id` = `Z`.`consumer_id`
> AND `X`.`move_date`<> `Z`.`move_date`
> AND `X`.`history_timestamp`>= `Z`.`history_timestamp`
> AND `Y`.`history_timestamp`<= `Z`.`history_timestamp`
> )
> GROUP BY `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp`
> ORDER BY `X`.`consumer_id`, `X`.`history_timestamp` ASC
>
> With Y I select history_timestamp preceding the current row with the
> same move_date.
> With Z I verify that no changes have occurred to move_date between
> X.history_timestamp and Y.history_timestamp.
>
That is not a valid PostgreSQL SQL statement.
Postgres does not use "backticks" for quoting, it uses the standard double quotes.
Thomas