Time, Events & Semi-Structured Data

In chapters 1 to 5 most rows were orders, customers or items. Now each row can be an event. An event is one thing that happened, such as a customer opening a page.

Event data brings two new problems. The first is time: every event has a timestamp, and time has traps. A timestamp is a date plus a time of day, such as 2026-01-03 09:15:00.

The second is JSON. JSON is text that holds named values, such as {"device":"ios"}. It is semi-structured data: the values have names, but the table has no fixed column for each one.

A payload is the JSON that an event carries with it. This chapter shows how to read it with SQL.

A pipeline is the program that loads data into your tables. To ingest data is to load it.

Two new tables

This chapter adds two made-up tables to the shop. The first gives each order a timestamp:

SELECT order_id, created_at
FROM order_times
ORDER BY order_id;

The second table lists eight web events. Each has two times and a JSON payload:

SELECT event_id, customer_id, event_name,
       event_time, ingested_at, payload
FROM web_events
ORDER BY event_id;

The queries in this chapter use PostgreSQL. The type timestamptz means timestamp with time zone. It stores the moment in UTC, and it does not keep the zone you typed.

A plain timestamp keeps the clock time and knows no zone. The session time zone is a setting of your connection. PostgreSQL shows each timestamptz in that zone, and here it is UTC.

The type jsonb stores JSON in a form that SQL can search. Where other databases use other names, the text says so.

To count events per day, first cut each timestamp down to its day. The cut value is a bucket. Then you group by the bucket.

In PostgreSQL, date_trunc does the cut. date_trunc('day', event_time) keeps the day and sets the time of day to 00:00:00:

SELECT date_trunc('day', event_time) AS event_day,
       count(*) AS events
FROM web_events
GROUP BY date_trunc('day', event_time)
ORDER BY event_day;

Events 401 to 404 all happened on 3 January, so they share one bucket. Events 405 and 406 share the bucket of 10 January.

date_trunc cuts at midnight in the session time zone, and here that is UTC. In a session set to Asia/Kolkata, events 405 and 406 land on 11 January. There, 23:55 and 23:40 UTC are 05:25 and 05:10 the next morning.

PostgreSQL 12 and later can name the zone: date_trunc('day', event_time, 'Asia/Kolkata').

For hours, change 'day' to 'hour'. The bucket is then the start of each hour:

SELECT date_trunc('hour', event_time) AS event_hour,
       count(*) AS events
FROM web_events
GROUP BY date_trunc('hour', event_time)
ORDER BY event_hour;

Other units work the same way, for example 'week' and 'month'. In PostgreSQL a week starts on Monday.

Bucket by event_time when your question is about when things happened. The section Event time vs ingestion time explains why.

Pick one day: half-open ranges

To pick one day, use >= the start of the day and < the start of the next day. This is a half-open range: the start is in, the end is out.

BETWEEN a AND b means >= a AND <= b, so both ends are in. See what happens when you use it for 10 January:

SELECT
  count(*) FILTER (
    WHERE event_time BETWEEN '2026-01-10' AND '2026-01-10'
  ) AS between_count,
  count(*) FILTER (
    WHERE event_time >= '2026-01-10'
      AND event_time <  '2026-01-11'
  ) AS half_open_count
FROM web_events;

The half-open range finds events 405 and 406. BETWEEN finds none, because both ends are the same instant, midnight.

A timestamptz is one exact moment. But the calendar day of that moment depends on where you are. At 23:40 UTC it is already the next morning in India.

The PostgreSQL tool is AT TIME ZONE. With a timestamptz it gives the clock time in the zone you name.

::date is a short way to write CAST(x AS date), from chapter 1. It keeps the date part:

SELECT order_id,
       created_at,
       (created_at AT TIME ZONE 'UTC')::date AS utc_day,
       (created_at AT TIME ZONE 'Asia/Kolkata')::date AS india_day,
       (created_at AT TIME ZONE 'America/Los_Angeles')::date AS la_day
FROM order_times
ORDER BY order_id;

Look at order 102 (Asha). At 23:40 UTC it is 05:10 on 11 January in India, so india_day moves one day later. Order 105 moves the other way in Los Angeles: it is 18:10 on 19 January there.

Orders 101, 103, 104 and 106 are on the same day in all three zones.

The day you choose changes the totals. Take the paid orders: 101, 102, 104 and 106. Group them by the India day:

SELECT (t.created_at AT TIME ZONE 'Asia/Kolkata')::date AS india_day,
       SUM(o.amount) AS revenue
FROM orders o
JOIN order_times t ON t.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY 1
ORDER BY 1;

AT TIME ZONE works the other way on a plain timestamp, one without a time zone. Then it reads the value as local time in that zone and gives back a timestamptz:

In chapter 5, ROW_NUMBER() numbered the rows inside each group. PARTITION BY made the groups. ORDER BY set the order.

Deterministic means the same result on every run.

Here ROW_NUMBER numbers each customer's events in time order. Look at events 402 and 403: they have the same event_time, 09:10:00. The query adds event_id to the ORDER BY as a tie-breaker:

SELECT customer_id,
       event_id,
       event_name,
       event_time,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY event_time, event_id
       ) AS seq
FROM web_events
ORDER BY customer_id, seq;

Events 402 and 403 tie on event_time. The tie-breaker puts 402 (the cart) first and 403 (the checkout) second.

Here it also matches the real order: a cart comes before a checkout. An id makes the order fixed, but not always true.

Without event_id, the database may return either row first. It makes no promise, and the choice can change from one run to the next. Then a "first event" or "last event" can change too.

Every event has two times. event_time is when it happened. ingested_at is when the pipeline stored it.

The gap between the two times is the delay.

You find the delay by subtracting one timestamp from the other. In PostgreSQL the result is an interval, a length of time:

SELECT event_id,
       event_time,
       ingested_at,
       ingested_at - event_time AS delay
FROM web_events
ORDER BY event_id;

Events 405 and 408 are late-arriving events: they reach the pipeline long after they happened. Here we call an event late when its delay is over 1 hour. Each team picks its own limit.

Late events change old reports. Suppose the report for 10 January ran at midnight, 2026-01-11 00:00 UTC. It could only count the events stored by then:

SELECT count(*) AS events_seen
FROM web_events
WHERE event_time >= '2026-01-10'
  AND event_time <  '2026-01-11'
  AND ingested_at <= '2026-01-11 00:00:00+00';

A report that runs again today finds 2 events for 10 January. The first run said 1. Both numbers are correct for their moment.

So ask which question you have. "What happened on 10 January?" uses event_time. "What did we know at midnight?" uses ingested_at.

An audit measures the problem. Count the events for each event day, and how many of them were late. FILTER (from chapter 3) counts only the rows that pass a test:

SELECT date_trunc('day', event_time)::date AS event_day,
       count(*) AS events,
       count(*) FILTER (
         WHERE ingested_at - event_time > INTERVAL '1 hour'
       ) AS late_events
FROM web_events
GROUP BY 1
ORDER BY 1;

On 10 January, 1 of 2 events was late (event 405). On 20 January, the only event was late (event 408). The other two days have none.

Once you know the size of the problem, you choose how to handle it. There are three common choices:

• Wait: close each day later, so late events can still arrive.
• Recount: run the last few days again each night, and replace the old numbers.
• Report: keep the late events, and publish how many there were.

Chapter 7, Data Engineering SQL Usage, shows how to replace old numbers safely. Chapter 8, Data Quality, Debugging & Auditing, shows more checks like this one.

Each value in a payload has a name. The name is called a key. The key "device" has the value "ios" in event 401.

To use a key as a column, you extract it.

PostgreSQL has two arrows: ->> returns the value as text, and -> returns it as jsonb, which is still JSON. First look at ->>:

SELECT event_id,
       payload->>'device' AS device,
       payload->>'campaign' AS campaign
FROM web_events
ORDER BY event_id;

Five events show NULL for campaign. In events 402, 406 and 407 the key is missing. In 403 and 404 the key is there, but its value is JSON null.

The next section tells these two cases apart.

Always give the extracted value an alias, like AS device. Then the column has a clear name.

JSON has its own types: string, number, boolean (true or false), null, array and object. jsonb_typeof tells you the type of a value.

Take events 401, 402 and 403 and look at the campaign key three ways:

SELECT event_id,
       payload->'campaign' AS campaign_json,
       jsonb_typeof(payload->'campaign') AS kind,
       COALESCE(payload->>'campaign', 'unknown') AS campaign_label
FROM web_events
WHERE event_id IN (401, 402, 403)
ORDER BY event_id;

In event 402 the key is missing, so -> returns SQL NULL. In event 403 the key holds JSON null, so -> returns the jsonb value null. jsonb_typeof shows the difference: NULL for missing, 'null' for JSON null.

The arrow ->> turns both into SQL NULL. So COALESCE gives 'unknown' for both.

That is fine for a label. It is wrong if "never set" and "set to nothing" mean different things.

An empty string is a third case. Suppose a payload has "campaign":"". That is a string, so ->> returns an empty text, not NULL, and COALESCE does not replace it.

The arrow ->> always returns text. To add or sort numbers you need a cast (chapter 1), which changes the type. In PostgreSQL you write ::numeric:

SELECT event_id,
       payload->>'amount' AS amount_text,
       (payload->>'amount')::numeric AS amount
FROM web_events
WHERE event_name = 'checkout'
ORDER BY event_id;

Text and numbers sort differently. As text, '100.00' comes before '25.00', because 1 comes before 2. After the cast, 100.00 is larger than 25.00.

An array is a list inside JSON, such as ["Pen","Notebook"]. In event 403 the key items holds an array of two products.

To count or group the items, turn the array into rows. In PostgreSQL, jsonb_array_elements_text gives one row for each item.

CROSS JOIN LATERAL lets it read the payload of each event. LATERAL runs the function once for each event row:

SELECT e.event_id, item
FROM web_events e
CROSS JOIN LATERAL
  jsonb_array_elements_text(e.payload->'items') AS item
ORDER BY e.event_id, item;

Eight events went in, but only five rows came out. Events with no items key give no rows, so they vanish from the result.

To keep them, use LEFT JOIN LATERAL with ON true. ON true means there is no condition: keep every row.

Each event without items then gives one row, with NULL for the item.

That makes 5 + 5 = 10 rows:

SELECT count(*) AS rows_out
FROM web_events e
LEFT JOIN LATERAL
  jsonb_array_elements_text(e.payload->'items') AS item ON true;

The grain changed. Before, one row meant one event. Now one row means one item of one event.

Event-level numbers repeat on every item row. So a sum can count an event twice:

SELECT SUM((e.payload->>'amount')::numeric) AS total
FROM web_events e
CROSS JOIN LATERAL
  jsonb_array_elements_text(e.payload->'items') AS item;

The sum is 40.00 + 40.00 + 25.00 + 80.00 + 80.00 = 265.00. Sum the amount before you expand. After you expand, use only item-level numbers, or count distinct event_id to count events.

A table checks its columns for you. A payload does not. Anyone can add a key, drop a key or change a type, and no error appears.

So you check the shape. This query counts the types of the campaign key. The result shows three kinds of event:

SELECT jsonb_typeof(payload->'campaign') AS campaign_kind,
       count(*) AS events
FROM web_events
GROUP BY 1
ORDER BY 1;

The counts add up to 8 events. If a later load brings a new kind, such as a number, you see it here first.

Four habits keep payload queries safe:

• Cast numbers and dates to their real type.
• Count missing keys and wrong types, as the query above does. Alert when the counts change.
• Make a key that you use often into a real column when you load the data.
• Keep the raw payload, so you can extract a key later that you did not plan for.

Each exercise opens in the SQL workspace with its own events table. Write the query, run it, and check the result.

The live workspace runs SQLite. It spells the JSON functions json_extract and json_each, and uses datetime() for times. The idea is the same as in this chapter.