Looker Studio Function : DATETIME_DIFF
Category: Arithmetic function
The DATETIME_DIFF function calculates the difference between two datetime values, returning the result in specified units (e.g., days, hours, minutes). It’s essential for analyzing trends, segmenting users, and making data-driven decisions.
Purpose of the DATETIME_DIFF Function
1. Data Distribution Analysis :
DATETIME_DIFF helps analyze how data spreads over time—such as customer signup frequency—by quantifying intervals, enabling precise distribution and gap analysis.
2. Customer Segmentation :
You can segment customers based on how recently or frequently they interact. For example, measuring days since last purchase helps target high‑value segments.
3. Decision Making :
By comparing time intervals (e.g., between marketing campaigns), DATETIME_DIFF supports decision‑making on when to repeat actions or optimize strategies for better ROI.
Type of Calculation & Practical Use Cases
✅ Performance Benchmarking
Compare activity intervals (e.g., processing time between events) to benchmark system or user performance over time.
✅ Customer Segmentation
Identify idle vs active users by measuring days since last login or purchase, enabling tailored re-engagement strategies.
✅ Time Series Analysis
Analyze trends by comparing intervals between data points, such as daily transactions or weekly active users.
DATETIME_DIFF(unit, datetime_expr1, datetime_expr2)
| Parameter | Type | Description |
|---|---|---|
unit | STRING | Unit to return: SECOND, MINUTE, HOUR, DAY, MONTH, YEAR |
datetime_expr1 | DATETIME | The ending timestamp (later date/time) |
datetime_expr2 | DATETIME | The starting timestamp (earlier date/time) |
How Does the DATETIME_DIF Function Work?
DATETIME_DIFF subtracts datetime_expr2 from datetime_expr1, then converts that interval into the specified unit. Use it when you need precise time differences—ideal for monitoring user activity, campaign performance, or event gaps.
Example of DATETIME_DIFF with Result
DATETIME_DIFF(‘DAY’, DATETIME ‘2025-06-18 12:00:00’, DATETIME ‘2025-06-15 12:00:00’)
Result :3
Explanation: Calculates the difference in days between June 18 and June 15.
Example : 1 Basic Usage
DATETIME_DIFF(‘DAY’, DATETIME ‘2025-06-15 10:00:00’, DATETIME ‘2025-06-10 08:30:00’)
Result : 5 (days)
Example 2: Column Usage
DATETIME_DIFF(‘HOUR’, last_event, first_event) AS hours_between_events
This shows the hour gap between the first and last event per record.
Example 3: Handling Decimals and Scientific Notation
DATETIME_DIFF returns integer intervals (e.g., 5 days). For fractional analysis, convert units to smaller granularity (e.g., seconds), then divide by larger units (e.g., seconds per day) to derive decimals.
Example 4: Combining with Other Functions
Use DATETIME_DIFF with:
CASEfor conditional segmentsIFto filter based on time spansAVG,SUMto aggregate time intervals
Tips and Tricks
Always ensure
datetime_expr1≥datetime_expr2for positive results.Use
MONTHorYEARfor broad trends,SECONDfor detailed analysis.Convert results to finer units for precise decimals.
Combine intervals with conditional segmentation.
Use
WITHclauses to isolate interval calculations cleanly.
It supports SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR, letting you tailor calculations from seconds to years based on your analysis needs.
Yes—if the first datetime is earlier than the second, it returns a negative result. Always order arguments to avoid unintended negatives in analytics.
No—only whole units. To achieve fractions, calculate in smaller units (e.g., seconds) and divide by unit conversions like 86,400 for days or 3,600 for hours.
Midnight boundaries are smooth: DATETIME_DIFF('DAY', '2025-06-15 00:00:00', '2025-06-14 23:59:59') returns 0, since it counts full intervals only.
Use it when you need precise intervals between events—like session duration, time since signup, or campaign gaps—to segment, benchmark, and support data‑driven decisions.