Preserving Local Time in Reporting: Why Relative Timestamps Matter
When working with data from various APIs, one common challenge is timestamp management. Different APIs might use multiple formats to represent time, but most provide an event_time field in ISO 8601 format. This format not only records the precise date and local time but also includes the time zone offset. For example, a timestamp like 2025-01-01T14:20:05-05:00 indicates 2:20:05 PM on January 1st, 2025 in Eastern Time (UTC−05:00).
Why Do We Store Timestamps in Relative Format?
At TapClicks, we store these timestamps in a relative format, meaning we preserve the original local time along with its time zone information. Here’s why this approach is critical:
- Consistency Across Campaigns and Connectors: Many businesses run campaigns across several regions and platforms. By storing timestamps as they were received—with their original local time and time zone—we keep the reporting consistent and true to source(no unintended shifts.).
- Clarity for End Users: Suppose you manage campaigns targeting different regions. Imagine you’re managing campaigns in both Eastern and Pacific Time. With relative timestamps, TapClicks shows events in each campaign’s local time—so “10 PM” means “10 PM” in that region. No mental time zone math required.
Example: Same Local Time, Different Zones
Imagine two calls occur on January 1st:
- One at 10 PM Eastern Time
- Another at 10 PM Pacific Time
Both will appear as "10 PM on January 1st" in TapClicks, reflecting their actual local event time. If all timestamps were converted to UTC or some fixed zone, you might see these events displayed as if they happened on different days (e.g., "3 AM the next day" for Pacific, "1 AM" for Eastern), due to the time difference. This can easily lead to confusion—especially when aggregating or comparing activity across campaigns.
Visualizing Hourly Data
You can verify this behavior by building a widget that aggregates events by hour. Grouping by the preserved local time provides clear, intuitive insights—midnight activity in New York shows up as "12 AM," while midnight in Los Angeles is still "12 AM" there, even if those events happened three hours apart.