What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Group by a month-start date or timestamp—not by the month number alone—and filter timestamps with an inclusive start and exclusive next-month boundary. That preserves the year in each group and includes every time value in the month, without relying on a guessed “last second.”
Why grouping by month can merge different years
EXTRACT(MONTH FROM event_time) returns a month number. If your data spans multiple years, grouping on that value alone combines every January, every February, and so on. Use a month-start value as the key, or include both year and month in the grouping.
A typed month-start date or timestamp retains the year and month, so January 2025 and January 2026 remain separate groups. Avoid using a formatted month name such as “January” as the key: it is not unique across years and may depend on locale. Keep a date or timestamp for grouping and sorting; format it for display afterward.
Free tools Windows power users keep installed
One-click scans. No signup required.
Use a half-open range to include the whole month
Filter the source timestamps with an inclusive beginning and an exclusive boundary at the beginning of the next month:
#1 Best Overall
WHERE event_time >= :month_start
AND event_time < :next_month_startFor January, the first boundary is January 1 and the second is February 1. The first instant is included; the next month’s first instant is not. This captures all representable times during January without guessing the column’s fractional-second precision or writing a fragile value such as 23:59:59 on January 31. Calculate both boundaries in the calendar used for the report.
If the column is a timestamp representing an instant, choose the reporting time zone before deriving the boundaries. An event near midnight can fall in different calendar months depending on the zone. PostgreSQL documents an explicit time-zone argument for truncating a timestamp with time zone, while BigQuery documents time-zone behavior for timestamp truncation. PostgreSQL date/time functions and BigQuery date functions describe the relevant behavior.
Month-start grouping by database
Use the syntax supported by your database version and match the function to the column’s type. Date and timestamp functions are not interchangeable in every dialect.
PostgreSQL
For a timestamp column and a UTC-like or otherwise intentionally configured session time zone, a query can look like this:
Rank #3
SELECT date_trunc('month', event_time) AS month_start,
count(*) AS event_count
FROM events
WHERE event_time >= timestamp '2026-01-01'
AND event_time < timestamp '2026-02-01'
GROUP BY date_trunc('month', event_time)
ORDER BY month_start;The sample boundaries are unzoned timestamp literals; use literals or parameters whose types and time-zone meaning match the column and reporting calendar. PostgreSQL’s date_trunc accepts the month field. For timestamp with time zone, truncation uses the current TimeZone setting unless a time zone is supplied explicitly. See PostgreSQL’s date/time function documentation.
SQL Server
On supported SQL Server versions, use DATETRUNC(month, event_time) to produce the month-start value. Microsoft also documents DATE_BUCKET, which returns the start of a date/time bucket. Verify that the target SQL Server version supports the function you choose before deploying it. See Microsoft’s DATETRUNC documentation and DATE_BUCKET documentation.
Rank #4
BigQuery
For a DATE, use DATE_TRUNC(date_value, MONTH). For a timestamp, use TIMESTAMP_TRUNC(timestamp_value, MONTH[, time_zone]); its time-zone behavior can be specified or defaulted. Choose the time zone that defines the reporting month. See BigQuery’s date functions and timestamp functions.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Choose the right grouping key
| Grouping key | Preserves year? | When it fits |
|---|---|---|
| Month-start date or timestamp | Yes | Usually the clearest single key for grouping, ordering, and displaying monthly periods. |
| Year and month as separate fields | Yes, if both are included | A valid alternative when the query or downstream schema needs separate numeric fields. |
| Month number alone | No | Only suitable when intentionally combining the same month across years. |
| Formatted month name | No | Not suitable as a unique key; names can also vary by locale. |
Whichever key you choose, confirm that the database supports the expression, that it returns a type appropriate for the query, and that timestamp grouping follows the report’s intended time zone.
Quick Recap
Best Value
Check a monthly query when rows seem to disappear
- Check whether the filter uses
>= month_startand< next_month_start, rather than an inclusive upper bound set to a guessed final second. - Confirm that the next-month boundary is calculated in the reporting calendar, especially when the source column represents instants.
- Check that the grouping key includes the year, either through a month-start value or separate year and month fields.
- Match the function and literal types to the database dialect, column type, and version.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
