Selecting by month in PostgreSQL

Need the right tool for the job

In PostgreSQL, the date functions are your go-to toolbox when dealing with date-time values. The EXTRACT function is one such handy tool, especially because PostgreSQL does not support the Month() function found in other SQL databases.

When numbers just aren't enough

If you're feeling a bit more poetic and prefer the month name over its numeric representation, TO_CHAR is your friend:

-- Shakespeare had July, we have June
SELECT TO_CHAR(your_date_column, 'Month') AS month_name 
FROM your_table 
WHERE TO_CHAR(your_date_column, 'Month') = 'June';


Avoiding the banana peels: Edge cases

While the routine usage is simple, ensure you bear these points in mind to dodge any curveballs:

• Time zones can be pain. Always normalize your dates if your data straddles multiple time zones.
• Leap years are fun...until they're not. Look out for them when crunching month-based calculations.
• The month extracted will be a number, so a casting spell may be needed if it's to dance with a string.

Range-based selection

Dealing with a range of months? Then a BETWEEN clause should do the trick:

-- The summer romance of April and July
SELECT * 
FROM your_table 
WHERE EXTRACT(MONTH FROM your_date_column) BETWEEN 4 AND 7;


Power boosting your queries

When you need to get through extensive datasets, consider the following for a feisty performance:

• Use date indices to fasten the selection process.
• Apply a range condition on the complete date before extracting the month.
• Shying away from column functions when selecting a range allows index scans.