Lessons Learned in Developing Interactive Time Exploration in Rill Dashboards

Time is an integral component of data analysis, and in the realm of data visualization and dashboard development, handling time effectively is crucial. In creating dashboards at Rill, we have invested significant effort in refining the approach to managing time, resulting in a robust and user-friendly system. In this blog post, we will delve into the foundational concepts of time in Rill and explore how the platform defines time ranges, comparison time ranges with period, handling of time zones, and other time related interactions.

Foundational Concepts of Time in Rill

All data is in motion, and time is the fundamental lens through which we make sense of data. To make time work seamlessly for users, we employ a set of foundational primitives shared by most programming languages and frameworks:

Point in Time

A specific point in time defined by an increment from an earlier fixed point, known as the epoch. Typically, the Unix epoch of ‘1970-01-01 00:00:00 +00:00’ serves as the fixed point, and increments are measured in microseconds or nanoseconds. This primitive goes by various names in different environments, such as timestamptz in Postgres, Time in GoLang, TimeStamp in Pandas, and DateTime in Luxon.

Duration

Duration represents a discrete length of time, mapping units of time (seconds, days, weeks) to a quantity of time. Expressions like “5 days” or “3000 seconds” relate time units to specific time spans. It is referred to as ‘interval’ in Postgres, ‘Duration’ in GoLang, ‘TimeDelta’ in Pandas, and ‘Duration’ in Luxon.

Time Range

A time range consists of a start point in time and an ending point in time. Crucially, all time ranges in Rill are half-closed intervals, inclusive of the start time and exclusive of the end time, denoted as ‘[start, stop)’. This allows for precise time segmentations.

Offsets

Offsets are durations that follow calendar rules, allowing users to define the duration required to add or subtract from a given point in time. While simple for seconds and hours, defining offsets becomes more complex when dealing with starts of days, months, and years, especially when incorporating timezones.

Rill uses these foundational primitives to create a set of aliases and functions that enhance the platform’s user experience and APIs. These include ‘Present time,’ ‘Offset aliases,’ ‘Time range aliases,’ ‘Time grain,’ and ‘Comparison time range aliases.’

Defining Time Ranges, Comparison Time Ranges, and Grains

One of Rill’s essential features is the ability to define time ranges, comparison time ranges, and grain (defined by an interval) at which this data is aggregated. To provide users with flexibility and control, Rill offers a range of time grains and smart default options for different time ranges:

Time Ranges with Supported and Default Time Grains

These time ranges serve as the building blocks for exploring data within Rill Dashboards. Users can choose the appropriate time grain to analyze their data effectively.

Primary and Comparison Time Range Selector Options

Rill Dashboards also offer primary and comparison time range selector options, allowing users to customize their time frame selections. These options are defined by time range aliases, which dynamically adjust based on the present time.

Rill’s time range aliases are designed to cater to various use cases, making it easy for users to select the time frame that suits their analysis. These aliases are defined by the present time and often utilize offset durations to generate specific time ranges. For example:

Last-N-Periods Aliases

• Last 6 Hours: This alias calculates the time range from 6 hours before the present time up to the present time. It is especially useful for tracking recent data within hourly granularity.
• Last 7 Days: Covering the previous week, this alias captures the time range from 7 days ago up to the present day.
  

Last-N-Periods Comparison Aliases

For last 6 hours we can have the following comparison aliases -

• Previous 6 Hours: This alias gives us a comparison range that spans the 6 hours preceding the currently selected range.
• Previous Day: This alias provides a comparison range that extends from a day before the start of the current selected range to a day before the end of the current selected range.

Period-to-Date Aliases

• Today: This alias begins at the start of the day and extends up to the end of day.
• Week to Date: This alias begins at the start of the current week and extends up to the present day, offering a weekly perspective on data.

Period-to-Date Comparison Aliases

For Week to Date some of the possible comparison options are -

• Previous Week Complete: This alias is ideal for comparative analysis, encompassing the entire previous week's data for a holistic view.
• Previous Week to Date: This alias starts at the beginning of the previous week and extends up to the same point in the present week, facilitating comparisons within the current time frame.
• Previous Month to Date: Similar to the previous week, this alias provides a comparison range from the start of the previous month up to the same point in the present month, ensuring consistent and insightful data analysis.

Similarly we can have Previous Quarter to Date, Previous Year to Date as well. These options help us in analyzing cases across various timespans. For example one can compare sales in Christmas week this year vs the same time last year.

Time Zone Support in Rill

Time zone support is a critical feature for any data analysis and visualization platform. Users often need to view and work with data in their local time zone, especially when collaborating across different regions

Understanding Time Zones

Time zones are named geopolitical regions where the same time is observed. The time in each time zone is defined as an offset from Coordinated Universal Time (UTC). These offsets are typically whole numbers of hours, but some time zones have additional 30 or 45-minute offsets. Some regions, like parts of North America and Europe, may also adjust their UTC offset during daylight saving time.

User Requirements

To address user needs effectively, Rill has defined several key requirements related to time zones:

Detection of Time Zones

Rill aims to automatically detect the user’s time zone using a best-effort inference from their browser. This detected time zone will serve as the default, but users can override it within the application.

Selection of Time Zones

Users should have the ability to select a different time zone manually. This choice should reflect in the rendering of charts and tables in the application, affecting how time ranges are evaluated.

Saved State for Time Zones Across User Sessions

Rill stores the selected time zone preference at the user-dashboard level. This means that users can have different time zone preferences for different dashboards.

Display of Time Zones in Dashboards

Time zones should be displayed in dashboards, using time zone abbreviations (e.g., PST) to minimize visual clutter.

Code Configure Time Zones

While many tools offer the option to change time zones, they often lack the flexibility to select a customized subset of time zones that align with your specific business requirements. In our case, we provide a code configuration feature that empowers you to define the set of time zones supported by your dashboard. This capability is particularly valuable for organizations with a presence in multiple geographic locations, as it enables seamless navigation and analysis of data across different relevant time zones, while overwhelming users with time zone options.

Challenges Faced While Working on Adding Time Zone Support

When it comes to adding time zone support to software applications, developers encounter several formidable challenges. Here, we delve into some of the key obstacles we faced during the implementation process:

Lack of a Global Standardized Database for Time Zone Abbreviations

One of the primary challenges is the absence of a globally standardized database for time zone abbreviations. This becomes particularly problematic when multiple regions share the same abbreviations. For instance, ‘IST’ could signify either Indian Standard Time or Israel Standard Time. To ensure accuracy, it’s essential to disambiguate these abbreviations correctly.

There is no good solution here. To mitigate this, we store an internal mapping of time zone IANA along with their expanded name in our application.

Browser Limitations in Handling Timestamps with Time Zones

Browsers come with inherent limitations when it comes to handling timestamps associated with specific time zones. To display timestamps accurately to users, we often need to convert them to the local time zone and vice versa. This conversion process adds complexity to rendering timestamps correctly.

Complexity of Daylight Saving Time

Daylight Saving Time (DST) introduces a whole new level of complexity in time-related calculations. The fact that DST rules can vary significantly across regions makes it challenging to calculate aggregates and perform time bucketing accurately. Handling transitions in and out of DST requires careful consideration.

These challenges highlight the intricacies of working with time zones in software development. To tackle them effectively, developers need to rely on robust libraries, databases, and best practices. Additionally, staying informed about time zone changes and updates is crucial to maintaining accurate and reliable time zone support in applications.

Visualizing Time Series Data and Handling Chart Interactions

At Rill, we take great pride in our ability to craft exceptional data visualizations, and our in-house charting framework built on top of D3 empowers us to do just that. This framework grants us the flexibility to tackle niche use cases and make intricate design choices, ensuring that our charts meet the unique needs of our users.

Data Flow

Behind the scenes, our time series charts in Rill rely on DuckDB SQL queries to fetch and aggregate data. When a user loads a dashboard, Rill orchestrates API calls to retrieve time series data based on selected measures, time grains, and time zones. Additionally, when requested, Rill fetches comparison time series data, offering a comprehensive view of the data landscape.

Animation

Animating time series visualizations gracefully, especially when underlying data changes, is a significant challenge. To address this, Rill employs a custom ChunkedLine component to render the line paths. This component cleverly breaks the line into segments and uses clipping techniques to gracefully handle data gaps. The line segments are smoothly transitioned when data changes occur.

Under the hood, our animation leverages d3-interpolate-path and Svelte’s core APIs to interpolate between different line paths. This approach allows for seamless transitions between lines comprising varying numbers of data points, maintaining visual coherence.

Trade-offs While Developing Charts

In the development of our charts, we’ve had to make thoughtful trade-offs to strike the right balance:

Center Aligned Buckets Over Left Aligned

While left-aligned buckets work well for high-density charts, they can be confusing in low-density line scenarios (e.g., fewer than 10 points). Moreover, changing time grains with left-aligned bins often results in a significant shift in the plotted time range. Center-aligned buckets offer a more intuitive and stable solution.

Using Lines Instead of Steps for Plotting

In scenarios where data is aggregated for a time bucket, displaying a constant value for that bucket makes sense. Although we considered a step-based approach for low-density charts, it didn’t scale well when dealing with partial start and end periods. Additionally, feedback from the community indicated that line charts are more familiar and intuitive.

Interactions

Rill offers a range of interactive features to empower users in exploring time series data:

Hover

When hovering over a line chart, crosshairs elegantly appear to display precise values at that specific point in time, along with the corresponding comparison value.

Scrubbing

With Rill, you can interactively click and drag horizontally across a chart to scrub through time. This feature allows you to gain deeper insights into a particular region of the chart, enabling a granular examination of your data.

Zooming

For users seeking a detailed analysis, Rill provides the ability to zoom in on a scrubbed region, facilitating drill-down exploration of their data. This zooming feature enhances the user’s ability to uncover critical insights within their time series data.

Panning

In our future release we are also going to add Pan interaction to our charts. Panning allows you to navigate horizontally across the chart, seamlessly moving through different time intervals. This added functionality ensures that you have complete control over your data exploration.

With our robust charting framework and interactive capabilities, Rill empowers users to visualize and interact with time series data in ways that uncover valuable insights and drive informed decision-making.

Conclusion

Developing interactive time exploration capabilities within Rill Dashboards has been a journey of refining foundational concepts, defining flexible time ranges and handling complex time related interactions and nuances. By focusing on these key elements, Rill empowers users to navigate and analyze time-series data with precision and ease, ultimately enabling more informed decision-making.

As the platform continues to evolve, users can expect an even more robust and intuitive experience when exploring time-based data. With Rill Dashboards, time is not just a dimension; it’s a powerful tool for unlocking insights and driving data-driven actions.

Check out these features and more - download Rill today. And connect with us in our Discord channel to share your feedback, or to get your questions answered.