Using Calendar and Time Dimension Tables to Standardize and Analyze Data Over Time

Summary

Mobile data collected from wearables comes in all shapes, sizes, and most importantly - time scales. Continuous glucose monitors (CGMs) provide glucose every minute or every five depending on brand. Sleep stages could be calculated every 30 seconds or summarized daily. Heart rate could be recorded every second or summarized over 5-second blocks, or even summarized over an exercise session. So how can order be brought to these unruly timescales? One powerful tool is a  Time dimension table, a sort of shared clock and calendar that your entire data model can plug into, regardless of the original source or device.

This article explains what these time dimensions are, why they matter for health research and business data analysis, and how they solve the common but frustrating problem of analyzing time-series data that sits at variable time scales. Ready-to-use code AI assistant prompts to generate these tables is freely available in the EFDC AI Prompt Database on GitHub, with support for multiple platforms including Power BI / Power Query (M), Python, R, SQL, JavaScript, Lua, PowerShell, and Bash.

What Are Time Dimension Tables?

In data analysis, a dimension table is a reference table that provides extra context about a specific kind of information. The most common example is a date or time dimension.

Think of it like this: your data (participant glucose readings, appointment records, billing transactions, sleep logs) contains timestamps. But a raw timestamp like 2025-03-15 14:35:22 doesn't immediately tell you:

• What fiscal quarter was this?
• What academic week was this?
• What 5-minute or 30-minute block of the day does this fall in?
• Was this a weekend?
• What ISO week number is this?

A Calendar table answers all of the "what date was this?" questions, for every single day in your date range. A Time slots table answers all of the "what time of day was this?" questions, broken into 5-minute or 30-minute intervals covering every minute of the day from midnight to midnight.

Once these tables exist, you simply connect your data to them — and all of those groupings and filters become instantly available in your dashboard or report. You never have to calculate them again.

Who Can Benefit From Using Time Dimensions?

You do not need to be a programmer or database expert to benefit from these tables — but understanding the concept will help you have better conversations with your data analyst or IT partner, and may open up new ways of thinking about your data.

Researchers Working with Wearables

If your study collects data from devices like continuous glucose monitors, smartwatches, activity trackers, or other sensors, you have likely encountered the challenge of devices recording on different schedules. A Calendar and TimeSlots table gives you a common frame of reference to align signals from different devices, compare data across participants, and analyze patterns by multi-second or muti-minute time slot, time of day, day of week, or even season of the year — without writing complex custom formulas for each dataset. This concept is key to the Common Data Model project at the Mobile Technologies Core and MDEN, which seeks to create a universal data model for clinically-relevant mobile device measurements.

Clinical Administrative Staff and Study Coordinators

If you manage participant data, generate progress reports, or support faculty with data summaries, Calendar and TimeSlots tables make it much easier to filter, group, and summarize data by meaningful time periods such as academic years, fiscal quarters, or specific weeks of a study. Rather than manually creating date groupings in spreadsheets, a pre-built Calendar table provides all of those groupings automatically for any date in your dataset.

Business and Operations Data Analysts

If you are building dashboards or reports for operational data — such as patient volumes, appointment metrics, billing cycles, or staff scheduling — a Calendar table is an industry best practice for date-based analysis. It allows you to easily switch between views (by day, week, month, quarter, fiscal year, or academic year) with a single click in your dashboard, without rewriting your underlying queries.

When Data Doesn't Tick on The Same Clock

One of the most common challenges in human-subject research utilizing mobile technologies is that every device manufacturer records data on its own schedule. Consider a study that collects different signals and user-entered data simultaneously:

If you try to put two of these signals side by side in a chart, you immediately run into a problem: the timestamps don't line up. A CGM reading at 11:35 and at 11:45, and an auto-delivered basal insulin dose at 11:41, and a carbohydrate log at 11:48 are all measuring the same general moment in time — but a naive comparison will either miss them entirely or create a confusing, jagged chart.

This gets even harder when you want to compare across participants who used different devices, or compare across studies that used different CGM models.

A Shared Time Grid

A TimeSlots table provides a single, consistent time grid that every device's data can be mapped onto. For example, a TimeSlots table could divide every 24 hours into 288 five-minute slots (from 12:00 – 12:04 through 23:55 – 23:59) and 48 thirty-minute slots (from 12:00 – 12:29 through 23:30 – 23:59).

To map your wearables data onto this grid, you match each reading's timestamp to the 5-minute slot it falls within, using a simple lookup. Then, for any slot that has multiple readings (because your device records more frequently than once per 5 minutes), you summarize using minimum, maximum, or average — depending on what makes clinical or scientific sense for that measure (or in some cases, what a regulatory body like the FDA mandates).

For example:

• Glucose: average glucose within each 5-minute slot gives a smoothed signal; maximum highlights peaks; minimum highlights troughs.
• Heart rate variability: average RMSSD within each 30-minute slot is a common choice for comparing across devices, and average within 30-second slot for combining with sleep measures.
• Sleep stages: majority (mode) within each 30-second or 5-minute slot captures what the participant was doing most of the time.

If you bring all signals on the same 5-minute grid, you can overlay them in a single chart, compare them across participants, and analyze them by time of day — regardless of which device collected them.

The Calendar table then layers on top of this, so you can further group by day, week, academic week, fiscal quarter, or any other time period that's meaningful for your research.

Picking a Time Scale

Picking a time scale is often a matter of meeting study objectives, business needs, or finding the common denominator among all data sources.

Five minutes is a practical sweet spot for wearables research because:

• Most CGMs record on a 1-minute or 5-minute cycle, so 5-minute slots capture CGM data without discarding readings.
• Most smartwatch and fitness tracker summaries align to 5- or 15-minute windows, making 5 minutes a common denominator.
• Five-minute resolution is fine enough to see meaningful within-day patterns (meal responses, activity bursts, sleep transitions) without producing unmanageably large datasets.
• Thirty-minute slots (6 consecutive five-minute slots) are available for broader trend views — for example, circadian patterns or sleep architecture.

If your study requires finer resolution, the same approach applies at the 1-minute level, or at the 30-second level. The code in the AI Prompt Database can be adapted accordingly.

However, you do not always have to pick a time scale! The beauty of dashboards is that you can slice and dice your data in multiple ways. When dealing with time series data, you can use Calendar and TimeSlots tables to essentially zoom-in and zoom-out, simply aggregating/summarizing each data type as you zoom out, or reveling the more detailed data as you zoom in.

Real-World Scenarios

For Wearables Researchers: Comparing Glucose and Sleep Across Participants

Suppose you are running a study where participants wear a CGM and a smartwatch simultaneously for 14 days. Half of your participants use a Dexcom G7 (records every 5 minutes) and the other half use a Freestyle Libre 3 (records every 1 minute).

Without a TimeSlots table: To compare glucose at 2 AM versus 2 PM across your cohort, you would need to write separate processing logic for each device type, bin the Libre's 1-minute readings into 5-minute buckets manually, and then reconcile the two datasets before any analysis.

With a TimeSlots table: Every reading from both devices gets matched to its 5-minute slot automatically. Average (or latest) glucose per slot is calculated once in your query. Your dashboard now shows a clean 24-hour glucose curve for every participant, with every device on the same scale, using the same time labels.

Adding the Calendar table then lets you filter by academic week, study week, or day of week to look at patterns like "do glucose levels differ between weekdays and weekends?" or "how does the participant's HRV change over the course of the study?"

For Clinical Administrative Staff: Tracking Data Completeness by Study Week

If you manage a wearables study, data completeness monitoring is critical. Participants may forget to charge their device, remove it during a medical procedure, or go out of sensor range.

A Calendar table makes it straightforward to report: "In study week 3 of this calendar year, Participant 047 had CGM data for 4 out of 7 days, and smartwatch data for 6 out of 7 days."

Without a Calendar table, producing this kind of weekly compliance report requires calculating week numbers manually for every participant, every week — which is tedious and error-prone in a spreadsheet. With a Calendar table, the week number is already there for every date, and the report writes itself.

For Business and Operations Analysts: Appointment Volume by Fiscal Period

If you manage operational data (clinic volumes, billing, staffing), a Calendar table with fiscal year and quarter columns makes period-over-period comparisons trivial. You can instantly answer questions like:

• How does patient volume in Q3 FY2025 compare to Q3 FY2024?
• Which academic weeks tend to have the lowest appointment availability?
• What time-of-day slots have the highest no-show rates?

The TimeSlots table adds the time-of-day dimension: by joining appointment timestamps to the TimeSlots table, you can see whether 30-minute blocks like 09:00–09:29 or 13:00–13:29 have systematically different patterns — without any manual time bucketing.

Zooming In and Out on Wearables Data

One of the most practical benefits of this approach is the ability to zoom in and out on your data simply by switching which column you group by — no changes to the underlying query needed.

In a dashboard tool like DataLaVista, Tableau or Power BI, you can overlay all data points in the same chart, which would adjust automatically based on the time scale you pick.

Getting Started

Prompts to generate Calendar and TimeSlots tables in your preferred tool are freely available in the EFDC AI Prompt Database:

https://github.com/DepressionCenter/AI-prompt-database/blob/main/data-analysis/time-dimension-table-prompts.md

The prompts have two parts: a base prompt containing the instructions for the logic in the Calendar and TimeSlots tables, which you must combine with the prompt for implementing in your specific platform or programming language.

The prompt set includes ready-to-use implementations for:

• Power BI / Power Query (M language) — generates both tables as native Power Query queries
• Python — generates pandas DataFrames and exports to CSV
• R — generates base R data frames
• SQL — generates tables using recursive CTEs (compatible with PostgreSQL, SQLite, DuckDB)
• JavaScript / AlaSQL — for use with DataLaVista or other browser-based dashboards
• PowerShell — exports to CSV
• Lua — in-memory tables or export to CSV and SQLite
• Bash — exports to CSV

Each prompt produces the same two tables with the same column structure, so your queries and dashboards are portable across tools.

Notes

• The Calendar table dynamically calculates its date range at runtime: it always covers from January 1st of (current year minus 10) through December 31st of (current year plus 5). No hard-coded years.
• Academic year is defined as September 1 through August 31, labeled as "YYYY-YY" (e.g., "2024-25").
• Fiscal year is defined as July 1 through June 30, labeled as "FYyyyy" (e.g., "FY2025") where the year reflects when June 30 falls. You can modify the prompt to match your company's fiscal year.
• ISO 8601 week numbering is used for CalendarWeek: Week 1 is the week containing the first Thursday of the year, and weeks start on Monday. This differs from Excel's default week numbering, which is not ISO 8601.
• The TimeSlots table uses an inclusive boundary convention: a 5-minute slot starting at 13:35 ends at 13:39, not 13:40. This prevents double-counting when matching timestamps that fall exactly on a boundary.
• When summarizing wearables data by time slot, the choice of aggregation function matters: average is appropriate for continuous signals like glucose or heart rate; maximum may be more meaningful for detecting peaks or getting body temperature; minimum for detecting nadirs; and mode (most common value) for categorical signals like sleep stages.

Resources

• EFDC AI Prompt Database (GitHub) — collection of real-world examples of UMGPT, Maizey, ChatGPT, Gemini, Claude and other AI prompts from the University of Michigan.
• Automators Anonymous Community of Practice (GitHub) — tidbits of code for Power Automate, Power Query, Power BI, Power Apps, SharePoint, AI and other cool technologies, from the members of the Automators Anonymous CoP at the University of Michigan / Michigan Medicine.
• DataLaVista — open-source, browser-based dashboard tool from the EFDC Mobile Technologies Core and Automators Anonymous. It can be embedded into SharePoint pages or any web apps.
• Mobile Technologies for Glucose Monitoring in Research
• 2026 MeTRIC Symposium Mobile Device Gallery: Wearables in Research
• Resources for Mobile Technologies Research
• Technology Elements & Budget Implications for Studies Utilizing Mobile Technologies
• Free Mobile Technologies Core consultations

About the Author

Gabriel Mongefranco is a Mobile Data Architect at the University of Michigan's Eisenberg Family Depression Center. Gabriel has over a decade of experience with automation, data analytics, database architecture, dashboard design, software development, and technical writing. He supports U-M researchers with data cleaning, data pipelines, automation and enterprise architecture for wearables and other mobile technologies.    |     |     |