Overview
The new reporting system is designed to deliver faster performance by pre-aggregating session data into multiple time bucket tables. This architecture enables efficient querying and reduces computational overhead when generating reports.
Time Bucket Architecture
Session data is summarized and stored in four different time bucket granularities:
- 10-minute buckets: For high-resolution, short-term analysis
- Hourly buckets: For medium-term reporting and analysis
- Daily buckets: For day-level aggregations and trends
Weekly buckets: For long-term trend analysis
Timezone Handling
Storage Standard
All data in the time bucket tables is stored in UTC timezone. This ensures consistency across different hotspot locations, as hotspots can be deployed in various timezones worldwide.
Query Translation
When a user requests session data in their local timezone, the system automatically translates the requested time range to the corresponding UTC time range before querying the database.
Standard Timezone Example
Scenario: A user in France (UTC+1:00) requests data from December 4, 2025, 00:00 - 12:00 (local time)
Process:
- The system converts the local time range to UTC: December 3, 2025, 23:00 - December 4, 2025, 11:00
- The system queries the hourly time bucket table for this UTC time range
- The data is returned and presented to the user in their local timezone
Edge Cases
Non-Hour-Aligned Timezones
Some timezones have offsets that are not aligned to full hours (e.g., UTC+5:30, UTC+9:30). This creates a challenge when querying hourly time buckets.
Example: A user in India (UTC+5:30) requests session data
Issue: The 30-minute offset means that the user's local hour boundaries do not align with the hourly time buckets stored in UTC.
Solution: The system shifts the query by half an hour to find the nearest time bucket data. This ensures that the most relevant data is retrieved, even though there may be a slight misalignment between the user's requested time range and the available time buckets.
Benefits
- Performance: Pre-aggregated data significantly reduces query time
- Scalability: Time bucket architecture handles large volumes of session data efficiently
- Consistency: UTC storage ensures uniform data handling across all timezones
- Flexibility: Multiple granularities support different reporting needs