MONTH

Extract the month component (1 through 12) from a date or timestamp.

Category: datetimeReturns: INTEGERDialect: Standard

Syntax

MONTH(date)

Description

## Overview Extracts the month component from a DATE or TIMESTAMP, returned as an INTEGER between 1 (January) and 12 (December). Equivalent to EXTRACT(MONTH FROM input). Use MONTH for seasonality analysis, calendar-month bucketing, and as a component of year-month cohort keys. ## Behavior - Returns an INTEGER in the range 1 through 12. - Returns NULL if the argument is NULL. - Accepts DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE inputs. - For TIMESTAMP WITH TIME ZONE, the month reflects the calendar month in the session time zone. - Month numbering is 1-based: January = 1, not 0. ## Timezone handling - TIMESTAMP WITH TIME ZONE is converted to the session zone before the month is taken. - Near a month boundary, the same instant can produce different month numbers in different session zones. ## Compatibility - Equivalent to the SQL standard EXTRACT(MONTH FROM source). The bare MONTH(x) form is widely supported.

Parameters

Name	Type	Description
`date`		Specifies the date or timestamp from which to extract the month.

Examples

-- July returns 7
SELECT MONTH(DATE '2025-07-04') AS month;

-- Current month
SELECT MONTH(CURRENT_DATE()) AS current_month;

-- Revenue by month of year across all years
SELECT MONTH(order_date) AS order_month, SUM(total_amount) AS revenue
FROM commerce.sales.orders
GROUP BY order_month
ORDER BY order_month;

-- Filter to first-quarter rows
SELECT event_id
FROM analytics.events.user_activity
WHERE MONTH(event_ts) BETWEEN 1 AND 3;

-- Combine with YEAR for yyyy-mm cohorting
SELECT YEAR(signup_date) AS yr, MONTH(signup_date) AS mo, COUNT(*) AS users
FROM analytics.users.engagement
GROUP BY yr, mo
ORDER BY yr, mo;

-- NULL passes through
SELECT MONTH(NULL::DATE) AS result;

Pitfalls

MONTH is 1-based (January = 1). Code that assumes 0-based month numbering from other platforms will be off by one.
Wrapping a column with MONTH(...) in a WHERE clause typically disables range index pruning. Prefer explicit date ranges for indexed filtering.
Near-midnight timestamps at month boundaries may fall into different months in different session zones. Be explicit with AT TIME ZONE when cross-zone comparability matters.