Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE animals__locations (
  date_time   timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL
, animal_id   integer     NOT NULL --   REFERENCES animals ON DELETE CASCADE
, location_id integer     NOT NULL --   REFERENCES locations ON DELETE RESTRICT
, id          serial      PRIMARY KEY
);

INSERT INTO animals__locations VALUES
  ('2023-04-17 11:11:11.000000 +00:00', 43, 55,11)
, ('2023-04-17 12:12:12.000000 +00:00', 44, 57,12)
, ('2023-04-17 01:01:01.000000 +00:00', 43, 45,1 )
, ('2023-04-17 02:02:02.000000 +00:00', 44, 47,2 )
, ('2023-04-17 21:21:21.000000 +00:00', 43, 65,21)
, ('2023-04-17 22:22:22.000000 +00:00', 44, 67,22)
;

CREATE TABLE



INSERT 0 6


-- "subset"
SELECT animal_id, date_time
FROM   animals__locations
WHERE  id IN (11,12);


animal_id
date_time


43
2023-04-17 12:11:11+01

44
2023-04-17 13:12:12+01



SELECT 2


-- previous
SELECT prev.*
FROM   (
   SELECT animal_id, date_time
   FROM   animals__locations
   WHERE  id IN (11,12)
   ) t -- "subset"
CROSS  JOIN LATERAL (
   SELECT *
   FROM   animals__locations t1
   WHERE  t1.animal_id = t.animal_id
   AND    t1.date_time < t.date_time
   ORDER  BY t1.date_time DESC
   LIMIT  1
   ) prev;


date_time
animal_id
location_id
id


2023-04-17 02:01:01+01
43
45
1

2023-04-17 03:02:02+01
44
47
2



SELECT 2


-- next
SELECT next.*
FROM   (
   SELECT animal_id, date_time
   FROM   animals__locations
   WHERE  id IN (11,12)
   ) t -- "subset"
CROSS  JOIN LATERAL (
   SELECT *
   FROM   animals__locations t1
   WHERE  t1.animal_id = t.animal_id
   AND    t1.date_time > t.date_time
   ORDER  BY t1.date_time
   LIMIT  1
   ) next;


date_time
animal_id
location_id
id


2023-04-17 22:21:21+01
43
65
21

2023-04-17 23:22:22+01
44
67
22



SELECT 2