SQL Time Between Events

The TIMESTAMP version of the same idea — subtracting two event timestamps gives an INTERVAL, precise down to the second.

What & Why

When both sides are TIMESTAMP rather than plain DATE, subtraction gives an INTERVAL — precise to the second, not just whole days. This is the right tool for questions like "how long between login and first click."

See How It Works

BUSINESS QUESTION

Growth wants the duration of every completed user session.

id	user_id	started_at	ended_at	page_views	device
5001	101	2024-01-16 14:20:00+00	2024-01-16 14:52:00+00	8	desktop
5002	102	2024-02-10 11:10:00+00	2024-02-10 11:18:00+00	3	mobile
5003	103	2024-03-13 08:10:00+00	2024-03-13 09:04:00+00	12	desktop
5004	104	2024-04-03 12:00:00+00	NULL	1	tablet
5005	105	2024-04-04 09:00:00+00	2024-04-04 09:21:00+00	7	mobile
5006	106	2024-04-05 10:15:00+00	2024-04-05 10:42:00+00	5	tablet
5007	107	2024-04-06 13:05:00+00	2024-04-06 13:38:00+00	10	desktop

EXAMPLE QUERY

SELECT
  id AS session_id,
  user_id,
  ended_at - started_at AS session_duration
FROM growth.sessions
WHERE ended_at IS NOT NULL
ORDER BY session_duration DESC, session_id;

RESULT — exact output from the displayed Queryflo rows

session_id	user_id	session_duration
5003	103	00:54:00
5007	107	00:33:00
5001	101	00:32:00
5006	106	00:27:00
5005	105	00:21:00
5002	102	00:08:00

The open session is excluded and completed sessions sort by duration.

Now You Try

Practice this concept

Growth wants the elapsed duration of every completed user session.

Available schema

growth

Prefix tables with growth.table_name.

session_iduser_idsession_duration

growth.sessions

Column	Type
id	bigint
user_id	integer
started_at	timestamp with time zone
ended_at	timestamp with time zone
page_views	integer
device	text
internal_session_score	integer

query.sql

Intermediate business practice