Looker Studio Function : DATETIME_TRUNC
Category: DATE function
DATETIME_TRUNC in Looker Studio rounds down a timestamp to the start of a specified time unit—like hour, day, or month. This streamlines data aggregation for time-based analysis and enhances insights, enabling clear trends, patterns, and segment distributions.
Purpose of the DATETIME_TRUNC Function
1. Data Distribution Analysis :
DATETIME_TRUNC groups raw timestamps—like transaction times or log entries—into uniform time slots. This reveals frequency distribution patterns over time, allowing clearer visualization of spikes, lulls, and overall data density.
2. Customer Segmentation :
By truncating customer events—such as signup dates—to days or months, you can segment cohorts based on activity timing. This enables tracking customer behavior patterns, onboarding success, or retention trends across segments.
3. Decision Making :
Aggregating metrics using truncated timestamps supports time-based performance comparisons. Decision-makers can measure weekly or monthly changes more accurately, helping guide strategy, budgeting, and resource allocation with confidence.
Type of Calculation & Practical Use Cases
✅ Performance Benchmarking
Compare KPI values (e.g. page views, revenue) across consistent time intervals—hours, days, quarters—ensuring apples-to-apples evaluation of performance.
✅ Customer Segmentation
Use DATETIME_TRUNC to group customer activity into weekly or monthly cohorts for retention analysis, lifecycle stage tracking, or targeted campaigns.
✅ Time Series Analysis
Smooth irregular timestamp data into uniform series for trend lines, seasonality detection, and forecasting using tools like Looker charts.
DATETIME_TRUNC(timestamp, unit)
timestamp (DATETIME): The original date-time value.
unit (STRING): Granularity level—options:
SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR.
How Does the DATETIME_TRUNC Function Work?
DATETIME_TRUNC “runs” by taking each timestamp and truncating it downward to the nearest specified unit. For example, 2025-06-19 13:45:27 truncated to DAY becomes 2025-06-19 00:00:00. Use it when you need grouped, consistent time buckets for analysis.
Example of DATETIME_TRUNC with Result
DATETIME_TRUNC(order_date, MONTH) AS month_start
| order_date |
|---|
| 2025-06-19 08:23:12 |
| 2025-06-02 15:45:00 |
| 2025-07-01 09:30:00 |
Result :
| month_start |
|---|
| 2025-06-01 00:00:00 |
| 2025-06-01 00:00:00 |
| 2025-07-01 00:00:00 |
Example : 1 Basic Usage
DATETIME_TRUNC(order_date, MONTH) AS month_start
Result : Transforms 2025‑06‑19 08:23:12 → 2025‑06‑01 00:00:00
Example 2: Handling Decimals and Scientific Notation
Since DATETIME_TRUNC works on date-time types, numeric formatting isn’t applicable—but when displaying timestamps downstream, ensure proper formatting via Looker Studio’s date/time controls.
Combining with Other Functions:
DATETIME_TRUNC(TIMESTAMP_ADD(order_date, INTERVAL 5 MINUTE), HOUR)
Useful for adjusted timezones or alignment before truncation.
Tips and Tricks
Always match truncation unit to analysis goal: use
DAYfor daily patterns,MONTHfor monthly trends.Use truncated outputs as join keys when blending different tables on time buckets.
Combine
DATETIME_TRUNCwithEXTRACT(e.g.EXTRACT(DAYOFWEEK FROM…)) to enrich time-based analysis.
DATETIME_TRUNC rounds a timestamp down to the start of a specified time unit (e.g., day, month). It’s used to group data consistently by time intervals for easier analysis.
Yes, use DATETIME_TRUNC(date_field, WEEK) to group timestamps by the beginning of the week, typically Sunday. This helps analyze weekly trends or compare weekly performance.
Absolutely. You can use DATETIME_TRUNC in calculated fields in Looker Studio charts, tables, and custom fields to transform or bucket datetime values as needed.
DATETIME_TRUNC works with your data source’s default timezone. For timezone-specific analysis, adjust timestamps beforehand using functions like DATETIME_ADD or DATETIME_SUB.
Use DATETIME_TRUNC to group full timestamp ranges (e.g., all events in a day). Use EXTRACT when you need individual date parts, like the day of the week or hour.