I basically have a table with date
, timestamp
, DID
, coordinates
.
I want a query that will return rows with the last coordinate from day X, and the first coord from day X+1, and the coordinates. So it will only return results where there are 2 consecutive dates.
This is what I came up with. Been trying to get this query to work, it's almost perfect, but I just need to add the commented out where
conditions and it'll do exactly what I want. But when I uncomment, I get an error "column doesn't exist":
SELECT a.timestamp_intersecting_date d1,b.timestamp_intersecting_date d2,
a."DID",
a.timestamp_intersecting_max t1, b.timestamp_intersecting_min t2,
RANK () OVER (
PARTITION BY a.timestamp_intersecting_date
ORDER BY a.timestamp_intersecting_max DESC
) timestamp_d1_rank ,
RANK () OVER (
PARTITION BY b.timestamp_intersecting_date
ORDER BY b.timestamp_intersecting_max ASC
) timestamp_d2_rank,
a.coords_centroid, b.coords_centroid
FROM
signals a
INNER JOIN signals b ON (a."DID" = b."DID")
WHERE (b.timestamp_intersecting_date = a.timestamp_intersecting_date + INTERVAL '1 DAY')
AND a."DID" = b."DID"
--AND timestamp_d1_rank = 1
--AND timestamp_d2_rank = 1
ORDER BY a."DID", t1 desc, t2 asc
How to solve this?
CREATE TABLE
statement showing exact data types and constraints. And your version of Postgres. Helps to avoid misunderstandings and makes it so much easier to help. You have a timestamp and a date?timestamp_intersecting_date
? A lot can go wrong right here already ...DID
has a (at most) a single row per day, you would use a different query than when there can be many. If time zones are involved, the definition of "day" may need some attention.