search postgresql by timestamp field

datona

I have a postgresql table I need to search through BUT I need to search by month. Example. I am using this sql statement now:
$sql1 = "SELECT SUM(in_bytes) FROM bandwidth_log WHERE site_id='1'";

but I would like to have the sum statement get all from a given month like "WHERE date=12" but the timestamp is in the format of: 2002-12-05 00:00:00-06

Can anyone give me a clue and/or answer.
Thanks

nnichols

I don't use PgSQL but give the following a try -

"SELECT SUM(in_bytes) FROM bandwidth_log WHERE site_id='1'
AND EXTRACT(MONTH FROM TIMESTAMP col_name)=12"

Might be completely wrong, but it's worth a try.

datona

Sorry but that does not work.

Weedpacket

As per the PostgreSQL documentation:

EXTRACT (field FROM source)

EG.
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
Result: 2

Where TIMESTAMP '2001-02-16 20:38:40' is the source. You'd want to replace that with something more appropriate

You've also got date_part:

date_part('field', source)

Note that here the field value needs to be a string. The valid field values for date_part are the same as for extract.

SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
Result: 16