Looker Studio Function : HOUR
Category: DATE function
The HOUR function in Looker Studio is a powerful time-based function used to extract the hour (0–23) from a datetime field. This function helps in breaking down data by time, revealing patterns in user activity, performance, or behavior across different times of the day.
Purpose of the HOUR Function
1. Data Distribution Analysis :
The HOUR function enables you to analyze how data points are spread across different hours of the day, helping identify peak activity periods or traffic drop-offs.
2. Customer Segmentation :
By segmenting users based on the hour of their interactions, you can uncover behavioral trends such as early-bird shoppers or late-night browsers, improving your targeted marketing strategy.
3. Decision Making :
Understanding hour-wise performance data helps business leaders make data-driven decisions, such as staffing during high-demand hours or launching campaigns when user engagement is highest.
Type of Calculation & Practical Use Cases
✅ Performance Benchmarking
The HOUR function helps benchmark performance by comparing key metrics—like sales, traffic, or conversions—hour-by-hour. This allows businesses to identify high and low-performing time slots and optimize operations or campaigns accordingly.
✅ Customer Segmentation
By extracting the hour of user activity, businesses can segment customers based on their active time of day. This time-based segmentation reveals behavioral trends and improves personalized targeting, marketing, and engagement strategies.
✅ Time Series Analysis
Using the HOUR function in time series analysis helps visualize trends within a 24-hour cycle. It allows analysts to track hourly patterns, detect anomalies, and make accurate short-term forecasts based on temporal behavior.
HOUR(datetime)
| Parameter | Type | Description |
|---|---|---|
datetime | DATETIME | The datetime field from which to extract the hour |
How Does the HOUR Function Work?
The HOUR function extracts the hour portion of a datetime field, returning a numeric value between 0 and 23. It operates on timestamped data, allowing you to analyze patterns or behaviors by specific hours of the day.
When Should You Use HOUR Function?
Use it when:
You want to analyze trends based on time-of-day.
You need to create time-based filters or segments.
You want to uncover peak or off-peak engagement hours.
Example of HOUR with Result
HOUR(Order_Timestamp)
Result :
2025-06-19 16:45:00
Result:16
Explanation:
The HOUR function extracts the hour part (16) from the datetime field Order_Timestamp, which represents 4:45 PM. This result can then be used to analyze time-based trends, segment users, or create hour-level filters.
Example : 1 Basic Usage
HOUR(Order_Timestamp)
Result:
If Order_Timestamp = 2025-06-19 14:35:00, result = 14
Example 2: Column Usage
Create a new column like Hour of Purchase to analyze sales distribution by time.
Example 3: Handling Decimals and Scientific Notation
The HOUR function only works with valid datetime fields—not decimals or scientific notation. Ensure proper formatting for accurate results.
Example 4: Combining with Other Functions
HOUR + CASE (Time Segments)
CASE
WHEN HOUR(Session_Start) BETWEEN 0 AND 11 THEN "Morning"
WHEN HOUR(Session_Start) BETWEEN 12 AND 17 THEN "Afternoon"
ELSE "Evening"
END
👉 Segments sessions by time of day.
HOUR + COUNT (Hourly Orders)
COUNT(HOUR(Order_Timestamp))
👉 Counts orders placed per hour.
HOUR + CONCAT (Label Hours)
CONCAT("Hour: ", TEXT(HOUR(Login_Time)))
👉 Displays hours with custom labels.
HOUR + IF (Peak Hour Flag)
IF(HOUR(Visit_Time) = 20, "Peak", "Off-Peak")
👉 Identifies peak vs off-peak hours.
HOUR + DATETIME_DIFF (Session Duration)
DATETIME_DIFF(Logout_Time, Login_Time, HOUR)Tips and Tricks
Use
HOUR(NOW())to get the current hour dynamically.Pair with filters (e.g.,
HOUR >= 8 AND HOUR <= 18) to isolate business hours.Use conditional formatting in charts for high-traffic hours.
The HOUR function extracts the hour (0–23) from a datetime field, allowing users to analyze time-based patterns, segment data by hour, and improve hourly performance insights in reports and dashboards.
No, the HOUR function only works with valid datetime fields. Applying it to text or improperly formatted data will return an error or null value in Looker Studio.
If the datetime value is null, the HOUR function returns null. Make sure your data source includes valid, non-empty datetime fields for accurate hour extraction.
You can use the HOUR function with CASE statements to group hours into segments like Morning, Afternoon, and Evening, making it easier to analyze user behavior across different time ranges.
Yes, you can filter reports by specific hours using the HOUR function. For example, use HOUR(Order_Timestamp) = 14 to display only orders made at 2 PM.