select values of a specific month from table contain timestamp column

Question

For example I have a following table(tbl_trans) like below

transaction_id  transaction_dte
integer         timestamp without time zone
---------------+----------------------------------
45             |  2014-07-17 00:00:00
56             |  2014-07-17 00:00:00
78             |  2014-04-17 00:00:00

so how can I find the tottal no.of transaction in 7th month from tbl_trans ?

so the expected output is

tot_tran  month
--------+-------
2         | July

Answer 1

select count(transaction_id) tot_tran
      ,to_char(max(transaction_dte),'Month') month from tbl_trans
where extract (month from transaction_dte)=7

PostgreSQL Extract function explained here

Reference : Date/Time Functions and Operators

Answer 2

select count(transaction_id),date_part('month',transaction_dte)
from tbli_trans where date_part('month',transaction_dte)=7

Answer 3

EXTRACT(MONTH FROM TIMESTAMP transaction_dte)

OR

date_part('month', timestamp transaction_dte)

You only need to add the word timestamp if your timestamp is saved in a string format

Properly looked up what the difference between the 2 is now:

The extract function is primarily intended for computational processing. For formatting date/time values for display.

The date_part function is modeled on the traditional Ingres equivalent to the SQL-standard function extract.

Answer 4

Use Datepart function.

where datepart(transaction_dte, mm) = 7