I have a table snapshot
, storing snapshots 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 NY
between 2006-02-01
and 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?
Aucun commentaire:
Enregistrer un commentaire