Search code examples
sqloracle-databasewindow-functions

Oracle SQL Query - Between Two Timestamp

For a report, i need to track movement of persons from one place to another within a given time range, based on their activities

Activities

Name TimeStamp Activity
Peter 10-JAN-23 05:23:06 Gym
Peter 10-JAN-23 07:01:45 Home
Peter 10-JAN-23 08:09:26 Restaurant
Peter 10-JAN-23 09:19:32 Office
Peter 10-JAN-23 16:43:02 Golf
John 10-JAN-23 07:30:26 Home
John 10-JAN-23 08:30:43 Gym
John 10-JAN-23 10:02:06 Theater
John 10-JAN-23 12:00:32 Soccer
John 10-JAN-23 20:23:02 Bar

From the above table, let's say we need to track movement of people from 8AM to 8PM (08:00 to 20:00) the result would be as below.

Name From To
Peter Home Restaurant
Peter Restaurant Office
Peter Office Golf
John Home Gym
John Gym Theater
John Theater Soccer

Using BETWEEN in WHERE CLAUSE the activity between the given range can be fetched. But I am unable to get the first 'FROM' place of each person as it falls outside the time range. I have tried with group by and window functions, but still unable to get the desired result. Can someone please help on this ?

Name From To
Peter ????? Restaurant
Peter Restaurant Office
Peter Office Golf
John ????? Gym
John Gym Theater
John Theater Soccer
Solution

  • Use the LAG analytic function in an inner-query and then filter on the time range in an outer query (if you do it the other way round then you will filter out the value before the start of the range before you can find it using LAG):

    SELECT name,
       prev_activity AS "FROM",
       activity AS "TO"
FROM   (
  SELECT a.*,
         LAG(activity) OVER (PARTITION BY name ORDER BY timestamp) AS prev_activity
  FROM   activites a
)
WHERE  timestamp BETWEEN TIMESTAMP '2023-01-10 08:00:00'
                     AND TIMESTAMP '2023-01-10 20:00:00';

    Which, for the sample data:

    CREATE TABLE activities ( Name, TimeStamp, Activity ) AS
SELECT 'Peter', TIMESTAMP '2023-01-10 05:23:06',    'Gym' FROM DUAL UNION ALL
SELECT 'Peter', TIMESTAMP '2023-01-10 07:01:45',    'Home' FROM DUAL UNION ALL
SELECT 'Peter', TIMESTAMP '2023-01-10 08:09:26',    'Restaurant' FROM DUAL UNION ALL
SELECT 'Peter', TIMESTAMP '2023-01-10 09:19:32',    'Office' FROM DUAL UNION ALL
SELECT 'Peter', TIMESTAMP '2023-01-10 16:43:02',    'Golf' FROM DUAL UNION ALL
SELECT 'John',  TIMESTAMP '2023-01-10 07:30:26',    'Home' FROM DUAL UNION ALL
SELECT 'John',  TIMESTAMP '2023-01-10 08:30:43',    'Gym' FROM DUAL UNION ALL
SELECT 'John',  TIMESTAMP '2023-01-10 10:02:06',    'Theater' FROM DUAL UNION ALL
SELECT 'John',  TIMESTAMP '2023-01-10 12:00:32',    'Soccer' FROM DUAL UNION ALL
SELECT 'John',  TIMESTAMP '2023-01-10 20:23:02',    'Bar' FROM DUAL;

    Outputs:

    NAME FROM TO
    John Home Gym
    John Gym Theater
    John Theater Soccer
    Peter Home Restaurant
    Peter Restaurant Office
    Peter Office Golf

    fiddle