# Design and Query Message History Data

Use this guide to plan the table structure, column roles, and timestamps for a Message History mapping. It also provides SQL examples for querying messages stored in EMQX Tables.

For instructions on creating a mapping, see [Message History](./message_history.md).

## Plan the Target Table

A Message History mapping selects values from messages that match an MQTT topic filter and writes them to one EMQX Tables table. Plan the table around messages that have a consistent structure, such as readings from the same device type or application.

When you plan the table:

- Use a separate table when message types have incompatible fields or value types.
- Map only the values that you need to query, filter, group, or analyze. Unmapped payload members do not create separate table columns unless you map the complete `payload` as a single Field.
- Keep the value type of each mapped column consistent across devices and messages. For example, do not publish a numeric `temperature` value in one message and a string value in another.
- Check the timestamp column, TTL, and column types before writing to an existing table. A mapping does not change these settings on an existing table.

If the target table does not exist, the first successful write creates it. The values in that write determine the column types, and later writes must use compatible types. The MQTT message example in the mapping wizard only previews the schema; it does not create or alter the table.

## Choose Tags and Fields

Use Tags for text values that identify or categorize a time series. Use Fields for measurements and other values that change over time.

| Data | Recommended Role | Examples |
| --- | --- | --- |
| Text used to filter or group records | Tag | `device_id`, `region`, `model` |
| Measurements and changing values | Field | `temperature`, `humidity`, `voltage` |
| Record time | Timestamp | Broker received time or a device timestamp |

Apply these mapping rules:

- Include at least one Field column.
- Do not configure numeric values as Tags.
- Store the complete `payload` only as a Field.
- A top-level array can be stored as one String Field. Nested arrays cannot be mapped individually.
- Use a unique name for every Tag, Field, and timestamp column.

For example, consider this payload:

```json
{
  "device_id": "sensor-001",
  "region": "east",
  "temperature": 23.5,
  "humidity": 58.5
}
```

A suitable mapping is:

| Source Field | Target Column | Role |
| --- | --- | --- |
| `payload.device_id` | `device_id` | Tag |
| `payload.region` | `region` | Tag |
| `payload.temperature` | `temperature` | Field |
| `payload.humidity` | `humidity` | Field |

This structure lets you filter or group records by device and region while calculating statistics from the measurements.

## Choose a Timestamp

Every Message History record requires a timestamp. Choose the timestamp source based on whether each record should represent the time EMQX received the message or the time the device produced the data.

| Timestamp Source | Use When | Considerations |
| --- | --- | --- |
| Broker Received Time | Devices do not provide a reliable timestamp, or you need the time when EMQX received the message | Does not represent the original event time when a message is delayed or buffered before publication |
| Payload timestamp | You need the time when the device produced the data | The device clock must be accurate, and the timestamp unit must match the configured precision |

For a Unix timestamp, select the precision that matches the value:

| Example Value | Precision |
| --- | --- |
| `1761206400` | Seconds (`s`) |
| `1761206400000` | Milliseconds (`ms`) |
| `1761206400000000` | Microseconds (`us`) |
| `1761206400000000000` | Nanoseconds (`ns`) |

Message History also recognizes RFC 3339 strings such as `2026-09-23T08:00:00Z` and `2026-09-23T16:00:00+08:00`. Include `Z` or an explicit UTC offset so that the timestamp does not depend on an assumed time zone.

If devices can buffer messages while offline, a payload timestamp can preserve the original measurement time. Broker Received Time instead records when the buffered message eventually reaches EMQX.

## Handle Payload and Schema Changes

Review a mapping whenever the device payload changes. Use the following guidelines:

- If a payload adds fields that are not mapped, Message History does not create separate table columns for them unless the mapping stores the complete `payload` as a single Field.
- To store a new field in an existing mapping, update both the Rule SQL and the corresponding EMQX Tables action write configuration in Data Integration. Alternatively, create a new mapping.
- If a mapped field changes type, writes to an existing table can fail because the table keeps its existing column type.
- If a field is no longer present in every message, test the changed payload before deploying it to all devices.
- If the new structure is incompatible with the existing table, create a new mapping and target table instead of mixing incompatible records.

Editing a rule or action changes the managed data integration directly. After an edit, publish a test message, check the mapping metrics, and query the target table to verify the result. Delete the mapping only from Message History so that EMQX Cloud can clean up its managed resources.

## Query Stored Messages

Open **Data Explorer** in the linked EMQX Tables deployment and select the mapping's database. The following examples use the `public` database, the `sensor_history` table, and a timestamp column named `timestamp`.

### Query the Latest Messages

Return the 10 most recent records:

```sql
SELECT *
FROM public."sensor_history"
ORDER BY "timestamp" DESC
LIMIT 10;
```

### Query Messages from One Device

Filter records by the `device_id` Tag:

```sql
SELECT "timestamp", temperature, humidity
FROM public."sensor_history"
WHERE device_id = 'sensor-001'
ORDER BY "timestamp" DESC;
```

### Query a Time Range

Return records whose timestamps fall within the last five minutes:

```sql
SELECT *
FROM public."sensor_history"
WHERE "timestamp" >= now() - '5 minutes'::INTERVAL
ORDER BY "timestamp" DESC;
```

### Aggregate Measurements

Calculate the average temperature for each device over the last hour:

```sql
SELECT device_id, avg(temperature) AS avg_temperature
FROM public."sensor_history"
WHERE "timestamp" >= now() - '1 hour'::INTERVAL
GROUP BY device_id;
```

EMQX Tables formats timestamp results using the Data Explorer client's time zone. For additional filters, functions, and time-window aggregation, see [Query Data with SQL](../emqx_tables/emqx_tables_query_sql.md).

## Related Links

- [Message History](./message_history.md)
- [Query Data with Data Explorer](../emqx_tables/emqx_tables_data_explorer.md)
- [Query Data with SQL](../emqx_tables/emqx_tables_query_sql.md)
- [Manage EMQX Tables Users](../emqx_tables/emqx_tables_user_management.md)