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.

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.
• Choose a consistent Data Type for each column, such as Float for measurements that may contain decimals. Use value transformations when conversion is needed.
• 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 converted 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.
• Tags must use String values. Convert numbers or Booleans to strings if needed. The complete payload, objects, arrays, and null cannot be 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:

{
  "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

Broker Received Time always uses milliseconds (ms). For a numeric payload timestamp, check that the suggested Time Precision matches the device's timestamp unit:

Example Value	Digits	Suggested Precision
1761206400	10	Seconds (s)
1761206400000	13	Milliseconds (ms)
1761206400000000	16	Microseconds (us)
1761206400000000000	19	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 a UTC offset. Selecting this source defaults Time Precision to milliseconds; you can change it if needed.

A field selected as Timestamp Source is removed from Tags and Fields and cannot use a custom conversion expression.

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:

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

Query Messages from One Device

Filter records by the device_id Tag:

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:

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:

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

Query results show timestamps in your local time zone, including milliseconds when nonzero. Switch to raw values to view full microsecond or nanosecond timestamps. For additional filters, functions, and time-window aggregation, see Query Data with SQL.

Related Links

• Message History
• Query Data with Data Explorer
• Query Data with SQL
• Manage EMQX Tables Users