I have a table
snapshot, storing snapshots (of the new state) every time an object stored in another live changed one of its attributes. I need to find all objects that changed a specific variable to a specific value in a given time frame.
For example, assume the following layout:
CREATE TABLE snapshot ( "timestamp" timestamp with time zone NOT NULL, person text NOT NULL, haircolor text NOT NULL, city text NOT NULL, CONSTRAINT snapshot_pkey PRIMARY KEY (person, "timestamp") )
Here, I want to find all
persons that moved (from any other city) to
2006-02-14. I want all of them, even the ones that moved in on
2006-02-03 and moved out on
2006-02-05 again. (And of course I do not want to list people who only changed their
haircolor in the given time frame).
What would an efficient query look like?