The hourly report starts one group at an odd minute. DATE_BUCKET makes interval boundaries explicit, including the origin that anchors them.
Define the Interval and Its Origin
DATE_BUCKET takes a date part, a positive bucket width, a date value, and an optional origin. It returns the start of the bucket containing the value. The origin anchors the repeating intervals. The default origin is fixed, but your business can need another anchor.
I ask what a week means for the report. A calendar week, payroll week, and production shift do not always start together. The SQL can group all three, but the origin must match the intended boundary.
Test timestamps immediately before, on, and after one boundary. Those three values reveal an off-by-one choice faster than a month of report output.
SELECT DATE_BUCKET(hour, 1,
CONVERT(datetime2(0), '2025-03-18T14:22:00'))
AS HourStart;Group Events by Hour With DATE_BUCKET
For an hourly report, DATE_BUCKET(hour, 1, EventTimeUtc) returns each UTC hour’s start. Group by that expression and count events. Keep EventTime in a consistent time basis. A local-hour report needs time zone rules before grouping.
I avoid formatting timestamps into strings to create groups. Formatting can hide type information and make ordering awkward. Keep a date and time value as the key, then format it at presentation.
A count by bucket has no row for an hour without events. Join to a generated time series or calendar when the report must show zero hours. An absent row is different from a measured zero.
SELECT DATE_BUCKET(hour, 1, EventTimeUtc) AS HourStartUtc,
COUNT_BIG(*) AS EventCount
FROM dbo.EventLog
GROUP BY DATE_BUCKET(hour, 1, EventTimeUtc)
ORDER BY HourStartUtc;Align Weekly Buckets With a DATE_BUCKET Origin
Weekly buckets depend on the origin. Choose a known start of the business week as a datetime value. That keeps the result independent of assumptions about week numbers. A fiscal week can require a separate calendar table if its rules change by year.
I check dates near year end and near the chosen week start. Those boundaries expose whether labels match the business calendar. A 4-4-5 fiscal calendar cannot be fully described by one generic weekly bucket.
This example uses a Monday origin. It illustrates alignment, not a universal week definition. Replace it with the approved business anchor.
DECLARE @origin datetime2(0) = '2025-01-06T00:00:00';
SELECT DATE_BUCKET(week, 1,
CONVERT(datetime2(0), '2025-03-18T14:22:00'),
@origin) AS WeekStart;
Use Custom Widths for Shifts
A width greater than one creates intervals such as fifteen-minute windows or four-hour shifts. The origin matters even more. A four-hour shift anchored at 6 a.m. differs from one anchored at midnight, though both use width four.
I put the shift anchor in configuration or a query variable. Hiding it inside a long expression makes later changes risky. Check daylight saving behavior when shifts are defined in local time. UTC blocks and local shifts can diverge around clock changes.
For a rolling interval starting at the first event, DATE_BUCKET is the wrong concept. It groups by fixed boundaries from an origin. Name the behavior before choosing the function.
DECLARE @shift_origin datetime2(0) = '2025-01-01T06:00:00';
SELECT DATE_BUCKET(hour, 4, EventTimeUtc, @shift_origin) AS ShiftStart,
COUNT_BIG(*) AS Events
FROM dbo.EventLog
GROUP BY DATE_BUCKET(hour, 4, EventTimeUtc, @shift_origin);Compare DATE_BUCKET With the Older DATEADD Pattern
Older SQL uses DATEADD and DATEDIFF from a fixed anchor for hourly boundaries. It still works on older SQL Server versions. The newer function makes the interval and origin visible. Check minimum supported version before replacing shared scripts.
I compare both expressions at boundary values. Different anchors can produce different buckets while both statements remain valid. A rewrite should preserve the report’s definition unless the owner approved a new one.
Do not claim the newer expression is automatically faster. Inspect plans and aggregation costs on representative data. Clearer SQL is a useful gain without an invented performance claim.
DECLARE @d datetime2(0) = '2025-03-18T14:22:00';
SELECT DATEADD(hour, DATEDIFF(hour, 0, @d), 0) AS OlderHourStart,
DATE_BUCKET(hour, 1, @d) AS BucketHourStart;Handle Local Time Carefully
A local day can contain 23 or 25 hours. A local hourly chart can repeat an hour when clocks move back. Convert UTC events to a named zone, then decide whether the repeated local hour should be distinguished by offset.
I prefer storing event instants in UTC and converting for display. For a business shift scheduled by local wall time, keep the zone and shift calendar as part of the rule. The bucket function alone cannot settle daylight saving policy.
Test spring and fall transitions for the region the report serves. A normal weekday sample is too polite to reveal time zone mistakes. A correct bucket function needs a correct time basis.
SELECT EventTimeUtc AT TIME ZONE 'UTC'
AT TIME ZONE 'Eastern Standard Time' AS LocalEventTime
FROM dbo.EventLog
WHERE EventTimeUtc >= '2025-11-02'
AND EventTimeUtc < '2025-11-03';Check Buckets Against Source Rows
Select events around each boundary and show their assigned bucket. Compare grouped totals with a direct count under the same filter. This catches missing ranges and mixed UTC/local assumptions. An aggregate can add up while labeling hours incorrectly.
For large reporting tables, consider a loaded bucket key when the grouping repeats and the time basis is stable. Measure its storage and update cost. A shift calendar can carry business labels that a generic interval function cannot.
The bucket function states interval size and origin in one place. Keep both aligned with the report’s meaning, test boundaries, and avoid treating a local wall clock as a fixed-length timeline.
A bucket is only meaningful when everyone agrees on its width and origin. A fifteen minute window anchored at midnight has different boundaries from one anchored at a later time. Which timestamp marks the bucket: its beginning or its end? Put that choice in the output name and in the chart label. I compare one event exactly on a boundary with one just before it, because an off-by-one bucket is easy to miss in a daily total.
Time zones add a second boundary problem. If source events use UTC, bucket in UTC for consistent elapsed intervals, then convert labels for a local report. If the business wants local calendar buckets, define how repeated and missing local hours are treated. Store the chosen origin and zone with the reporting rule. DATE_BUCKET simplifies arithmetic, but it cannot decide the meaning of a reporting day.
Which chart consumes these groups, and does it expect empty buckets to appear? The bucket function only labels rows that exist. For a continuous time axis, join the grouped results to a calendar or generated sequence of expected intervals. I check the first and last bucket in the requested window so a missing edge does not disappear unnoticed.
Related reading on this blog: A Walkthrough: DATETRUNC Function in SQL Server and Function to Round Up Time to Nearest Minute Interval.
A time bucket is not just a rounded timestamp, it is a boundary anchored to a business rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
