Have to count all records for a given month of a certain year in PostgreSQL table. The column being selected with is of type 'timestamp without time zone'. Let's call the column 'fdt', and count records for October 2012.
There are at least 3 solution options:
1.
SELECT COUNT(*) FROM table WHERE date_part('month', fdt) = 10 AND date_part('year', fdt) = 2012 ;
2.
SELECT COUNT(*) FROM table WHERE EXTRACT(MONTH FROM fdt) = 10 AND EXTRACT(YEAR FROM fdt) = 2012;
3.
SELECT COUNT(*) FROM table WHERE fdt >= '2012-10-01' AND fdt < '2012-11-01';
Third option is, in general, faster than the first two. For the same data options 1. and 2. count 22806 records in ~80 ms, and option 3 ~60ms.