Group by a month-start date or timestamp, not by the month number alone, and filter with an inclusive start and exclusive next-month boundary. That preserves the year in each monthly group and includes every timestamp in the target month without relying on a guessed “last second.”
Use a month-start value as the grouping key
A month number from EXTRACT(MONTH FROM event_time) is only 1 through 12. Grouping on it alone merges January from every year into one row. A typed month-start value retains both year and month, so January 2025 and January 2026 remain separate groups. Keep that date or timestamp for grouping and sorting; format it as a month name only for display, since labels such as “January” are not unique across years and can depend on locale.
As an Amazon Associate I earn from qualifying purchases.
PostgreSQL
For a PostgreSQL timestamp column, a month report can use date_trunc:
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;PostgreSQL documents date_trunc with a month field. For timestamp with time zone, truncation uses the current TimeZone setting unless a time zone is specified explicitly. Choose the reporting zone deliberately when grouping instants. See the PostgreSQL 18 date/time functions documentation.
#1 Best Overall
SQL Server
On supported SQL Server versions, use DATETRUNC(month, event_time) to produce the month boundary. Microsoft also documents DATE_BUCKET, which returns the start of a date/time bucket. Confirm that the target SQL Server version supports the function you choose before deploying it. See Microsoft’s DATETRUNC documentation and DATE_BUCKET documentation.
BigQuery
For date values, BigQuery provides DATE_TRUNC(date_value, MONTH). For timestamps, use TIMESTAMP_TRUNC(timestamp_value, MONTH[, time_zone]); timestamp truncation can use a specified time zone or the default behavior. See BigQuery’s date functions documentation and timestamp functions documentation.
Filter a month with a half-open range
For source timestamps, use a lower bound that is included and a next-month bound that is excluded:
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →WHERE event_time >= :month_start
AND event_time < :next_month_startSet :month_start to the first day of the reporting month and :next_month_start to the first day of the following month, using the column’s type and the intended reporting calendar. This range includes every representable time in the month. A filter ending at a guessed value such as 23:59:59 can miss rows with finer fractional precision.
Keep filtering and grouping conceptually separate: the range selects the source rows, while the month-start expression assigns each selected row to its group. The example uses a fixed January 2026 range; for a reusable report, bind parameters or compute the two boundaries in the application or query according to the report’s calendar.
Choose the calendar time zone for timestamp data
A timestamp representing an instant can fall in different calendar months depending on the reporting time zone. Decide which zone defines the month before deriving either the grouping key or its boundaries. PostgreSQL’s date_trunc for timestamp with time zone and BigQuery’s timestamp truncation support explicit time-zone behavior; otherwise a default setting may determine the result. The boundary values must follow the same intended calendar as the grouping expression.
When separate year and month fields are appropriate
A month-start key is usually easier to read and sort. An alternative is to group by both year and month, such as a year field together with EXTRACT(MONTH FROM event_time). That is valid only when both fields participate in the grouping; the month field alone is not a unique key across years. Use the database’s native date or timestamp functions and types rather than assuming syntax transfers unchanged between database systems.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Quick Recap
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.
