Unix Timestamps in Embedded Systems, APIs and Databases
Design sensor timestamps with RTC, NTP, monotonic clocks, JSON and SQL, including an ESP32 synchronization example.
A useful sensor record needs a value, a trustworthy time reference and enough context to explain uncertainty. Unix time supplies an interoperable event coordinate, but does not guarantee clock accuracy or ordering. Use Unix Timestamp / Epoch Converter to inspect captured values and Understanding Unix Timestamp and Epoch Time for the underlying representation.
RTC and system clock
A hardware real-time clock can retain a calendar or counter while the main CPU sleeps, often using a backup supply and a low-frequency oscillator. It is not necessarily configured for UTC and is not automatically synchronized. A system wall clock maps a hardware time base to an epoch; firmware or an operating system maintains that mapping. Validate the RTC after power loss rather than assuming its default date is real.
STM32, ESP32 and embedded Linux
On STM32, RTC features, backup domains and subsecond registers depend on the exact family; use its reference manual and ST AN4759. Convert the RTC calendar deliberately and avoid hard-coding a universal STM32 timestamp register. On ESP32, ESP-IDF combines RTC and high-resolution timing facilities according to configuration. On Linux, CLOCK_REALTIME provides civil time while a monotonic clock supports duration measurement. Check the libc and ABI as well as the CPU architecture.
NTP and SNTP synchronization
NTP estimates clock offset and network delay from packet exchanges. SNTP provides a simpler client approach; neither makes network latency disappear. NTP's packet epoch and era fields differ from Unix seconds: let the stack perform the translation. Record synchronization state, age and an uncertainty estimate, and decide how to handle a large correction. A step changes wall time suddenly; slewing changes the rate until the correction is absorbed.
Wall time versus monotonic time
Wall time answers when an event occurred in UTC and can jump after an administrator or synchronization service adjusts it. A monotonic counter measures elapsed time against an arbitrary origin. It is not a UTC date and may reset at boot; suspend behavior depends on the selected clock. Use a monotonic deadline for retries and timeouts, and pair samples with UTC when cross-device correlation is required.
Timestamp the acquisition, not just the upload
Capture the timestamp close to the ADC sample or interrupt that defines the measurement. Upload time includes buffering, radio scheduling and retries. Keep acquisition time and server reception time as separate fields. At high rates, timestamp a block boundary and store the sampling period and sequence, while accounting for oscillator drift. A sampling and aliasing guide helps distinguish temporal sampling from clock synchronization.
Event logs and telemetry
Include device identity, boot identity and a sequence counter so that repeated UTC values do not overwrite records after a backward clock step. Distinguish measured time from reconstructed time, and retain raw observations when later correction is possible. A global UTC sort alone is insufficient for a strict causal order between devices whose uncertainty intervals overlap.
Choose a unit from the error budget
| Timestamp unit | 1 s | 1700000000 s |
|---|---|---|
| Seconds (s) | 1 | 1700000000 |
| Milliseconds (ms) | 1000 | 1700000000000 |
| Microseconds (µs) | 1000000 | 1700000000000000 |
| Nanoseconds (ns) | 1000000000 | 1700000000000000000 |
Seconds suit coarse schedules; milliseconds often suit web telemetry; microseconds can support acquisition timing; nanoseconds may be appropriate for hardware timestamping. Resolution is the spacing of representable values, precision describes repeatability, and accuracy concerns agreement with the reference. More digits do not repair a drifting crystal. A 20 ppm free-running error accumulates about 1.728 s per nominal 24 hours; temperature and aging can change that error.
REST APIs and JSON
{
"timestamp_ns": "1700000000123456789",
"captured_at": "2023-11-14T22:13:20.123456789Z",
"time_quality": "synced",
"uncertainty_ms": 20,
"boot_id": "device-session-42",
"sequence": 17
}
Publish the epoch, unit, signedness and permitted range in the API schema. Encode large microsecond or nanosecond values as decimal strings where clients may use binary64 numbers. A BigInt cannot be passed directly to JSON.stringify. An explicit timestamp_ns name avoids heuristic unit detection. Define missing or unsynchronized time as null plus quality metadata, not a plausible-looking date near 1970.
ISO 8601 and RFC 3339
RFC 3339 is a useful restricted profile for internet timestamps. Include Z or a numeric offset and a documented fractional-second precision. For example 2023-11-14T22:13:20Z and 2023-11-14T23:13:20+01:00 identify the same instant. A bare 2023-11-14T23:13:20 cannot establish UTC without an agreed zone. An offset does not preserve the full future rules of Europe/Paris.
SQL storage
CREATE TABLE sensor_measurement (
device_id text NOT NULL,
boot_id text NOT NULL,
sequence bigint NOT NULL,
captured_at timestamptz,
monotonic_us bigint NOT NULL,
time_quality text NOT NULL,
temperature_c double precision NOT NULL,
PRIMARY KEY (device_id, boot_id, sequence)
);
INSERT INTO sensor_measurement VALUES ('sensor-1', 'boot-42', 17,
TIMESTAMPTZ '2023-11-14T22:13:20.123456Z', 9812370, 'synced', 23.625);
This example targets PostgreSQL. timestamptz stores an instant and renders it in the session's zone; it does not preserve the original IANA identifier. Store that identifier separately if the product needs it. PostgreSQL timestamp precision is microseconds, so keep an additional integer or decimal field when exact nanoseconds matter. Other SQL engines have different semantics and ranges. Index acquisition time for time-range queries, while using device, boot and sequence for identity.
UTC and local scheduling
Store event instants in UTC. A recurring instruction such as 08:00 every day in Paris needs the local rule and IANA identifier, since the corresponding UTC time changes with DST. Define what a schedule does when its local time is skipped or repeated. Adding 86400 Unix seconds is a duration operation; it need not produce the same local clock time on the following calendar day.
time_t and long-lived products
Do not assume the size or representation of time_t from sizeof(void*). A signed 32-bit seconds field encounters 2038 even if another part of the system is 64-bit. ESP-IDF uses signed 64-bit time_t from v5.0, but old firmware and protocol fields can remain narrow. Audit format strings, database columns, bootloaders, RTC drivers and OTA migrations. Use fixed-width storage contracts and explicit range checks at boundaries.
ESP32 example: NTP-synchronized sensor logging
The following ESP-IDF 5.x/6.x example is called once after the application has initialized esp-netif and connected Wi-Fi or Ethernet. The board-specific sensor driver calls log_sensor with a finite temperature value. The example uses the official esp_netif_sntp API, waits at most 15 seconds, and prints one JSON record per acquisition.
#include <stdbool.h>
#include <stdint.h>
#include <inttypes.h>
#include <stdio.h>
#include <sys/time.h>
#include "freertos/FreeRTOS.h"
#include "esp_netif_sntp.h"
#include "esp_timer.h"
#include "esp_random.h"
static bool clock_synced;
static int64_t last_sync_us;
static uint32_t boot_id;
static uint64_t sequence;
void ntp_after_network_ready(void) {
boot_id = esp_random();
esp_sntp_config_t config = ESP_NETIF_SNTP_DEFAULT_CONFIG("pool.ntp.org");
esp_netif_sntp_init(&config);
clock_synced = esp_netif_sntp_sync_wait(pdMS_TO_TICKS(15000)) == ESP_OK;
if (clock_synced) last_sync_us = esp_timer_get_time();
}
void log_sensor(double temperature_c) {
int64_t monotonic_us = esp_timer_get_time();
struct timeval tv;
bool valid = clock_synced && monotonic_us - last_sync_us < INT64_C(3600000000)
&& gettimeofday(&tv, NULL) == 0;
printf("{\"boot_id\":\"%08" PRIx32 "\",\"sequence\":%" PRIu64
",\"monotonic_us\":\"%" PRId64 "\",\"timestamp_us\":",
boot_id, sequence++, monotonic_us);
if (valid) {
int64_t utc_us = (int64_t)tv.tv_sec * INT64_C(1000000) + tv.tv_usec;
printf("\"%" PRId64 "\"", utc_us);
} else printf("null");
printf(",\"time_quality\":\"%s\",\"temperature_c\":%.3f}\n",
valid ? "synced" : "unsynced", temperature_c);
}
timestamp_us and monotonic_us are decimal strings. A failed synchronization emits null UTC while retaining acquisition order. The one-hour validity window is an illustrative product policy, not an accuracy guarantee. The example deliberately does not renew confidence after later automatic synchronization; production firmware should update last_sync_us from a synchronization notification, serialize shared state, track uncertainty, and handle network recovery. A random 32-bit boot identifier is illustrative; use a persistent counter or sufficiently strong identifier where collision resistance matters.
Restart, drift and loss of synchronization
After reset, a monotonic counter establishes a new timeline. Persist a boot identifier and avoid treating it as continuous with the previous boot. During network loss, propagate uncertainty according to the oscillator budget and last accepted synchronization. Read a credible backed-up RTC when available and label the quality accordingly. Large wall-clock corrections should be observable in telemetry; avoid silently rewriting historical measurements.
Design practices and verification
- Keep unit, epoch and signedness explicit across every interface.
- Use UTC for events and monotonic clocks for deadlines.
- Preserve sequence and synchronization quality alongside the timestamp.
- Test cold boot without network, RTC battery loss, backward corrections, DST folds and integer boundaries.
- Keep time-zone data current when future civil schedules are part of the product.
Technical FAQ
Does successful NTP synchronization guarantee microsecond accuracy?
No. Packet asymmetry, oscillator error, scheduling and acquisition latency remain. Measure the complete path and attach an uncertainty appropriate to the application.
Can I derive UTC from a monotonic counter alone?
No. You need a correspondence with a UTC reference and a model of drift. That mapping has a scope, synchronization age and uncertainty; it is invalid across an unrelated reboot.
Should I convert everything to local time before storage?
Keep event instants in UTC and convert for presentation. Preserve a separate zone when local scheduling or the original civil context matters.
Tools and primary references
Inspect a log with Unix Timestamp / Epoch Converter. Continue with Understanding Unix Timestamp and Epoch Time and the oscillator tolerance guide.
- IETF · RFC 3339
- IETF · RFC 5905 (NTP)
- IETF · RFC 8259 (JSON)
- Python · PEP 418
- Espressif · ESP-IDF
- STMicroelectronics · AN4759
- PostgreSQL · SQL
Related tools and guides
Crystal Load Capacitor Calculator · Read the complete design guide