DATE_BUCKET: Align 15-Minute Windows With an Origin

DATE_BUCKET aligns time windows from an origin rather than merely removing smaller time parts. I supply the origin when the reporting boundaries have a business meaning. Two fifteen-minute schemes can group the same event differently.

Make the origin part of the window contract

The query compares two explicit datetime2 origins. One begins at midnight, while the other begins at 08:05. Both use a width of fifteen minutes.

The midnight scheme has boundaries at 08:00, 08:15 and 08:30. The shifted scheme has boundaries at 08:05, 08:20 and 08:35. Equal widths do not imply equal starting points.

I include events immediately before and exactly on each shifted boundary. That reveals which bucket start is selected. A list containing only middle-of-window events could hide the alignment rule.

The query returns formatted bucket starts beside the source timestamp. Each formatted column has a fixed display length. The underlying date and origin expressions use the same explicit datetime2 type.

WITH Events AS
(
    SELECT EventId,EventTime
    FROM (VALUES
        (1,CAST('2026-01-15T08:04:59' AS datetime2(0))),
        (2,CAST('2026-01-15T08:05:00' AS datetime2(0))),
        (3,CAST('2026-01-15T08:19:59' AS datetime2(0))),
        (4,CAST('2026-01-15T08:20:00' AS datetime2(0))),
        (5,CAST('2026-01-15T08:34:59' AS datetime2(0))),
        (6,CAST('2026-01-15T08:35:00' AS datetime2(0))),
        (7,CAST(NULL AS datetime2(0)))
    ) AS v(EventId,EventTime)
)
SELECT EventId,CONVERT(char(19),EventTime,126) AS EventText,
    CONVERT(char(19),DATE_BUCKET(minute,15,EventTime,
        CAST('2026-01-15T00:00:00' AS datetime2(0))),126) AS MidnightAligned,
    CONVERT(char(19),DATE_BUCKET(minute,15,EventTime,
        CAST('2026-01-15T08:05:00' AS datetime2(0))),126) AS ShiftAligned
FROM Events
ORDER BY EventId;

Read the event before the origin

The first event occurs at 08:04:59. Its expected midnight-aligned bucket begins at 08:00. Its shifted bucket begins at 07:50, the preceding fifteen-minute boundary relative to 08:05.

That event precedes the supplied shifted origin on the same date. The origin establishes alignment; it is not a filter that excludes earlier events. If a report needs a starting cutoff, specify that separately.

I wouldn’t label the first shifted bucket as 08:05 merely because that is the chosen origin. Doing so would place the event after a boundary it has not reached. The expected preceding start makes that mistake visible.

Keep exact boundaries in the test

The event at 08:05 is expected to start the 08:05 shifted bucket. The event at 08:19:59 still belongs to that bucket. The event at 08:20 begins the next one.

The last two present events similarly separate 08:34:59 from 08:35. Their expected shifted starts are 08:20 and 08:35. The midnight-aligned results differ because their boundaries occur five minutes earlier.

A bucket start is not the original event timestamp. Several events can share that label. Preserve EventTime when later analysis needs the individual event order or exact occurrence time.

The missing timestamp has expected NULL bucket outputs. It does not belong to the midnight bucket by default. Decide explicitly whether missing event times should be excluded from any later grouped report.

Separate grouping from filtering and time-zone rules

Grouping by a bucket can summarize rows sharing its start. It does not automatically return empty buckets with no events. A report needing every interval must supply that interval population separately.

This example uses SQL Server 2022 or later. Its width is a positive integer, and both origins match the timestamp type. It changes no settings and does not depend on the current clock.

The timestamps contain no time-zone information. An origin does not infer daylight-saving transitions or attach a named zone. Establish a common temporal basis before grouping events from different locations.

I’d preserve the width, unit and origin alongside a reporting definition. Changing any of those can change which events share a group. A graph label saying fifteen minutes alone is not the complete contract.

Include events around the report’s actual day boundary when choosing an origin. Keep exact-boundary and just-before values together. Those cases show whether a small alignment change moves an event into a different reporting interval.

Pick the origin on purpose, and your windows will line up every time.

A window width is not a grouping rule, it is only half of one without an origin.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.