In SQL(postgresql) How to group based on a "timestamp without time zone" column?

7
votes

I am trying to write some queries to group things based on each month in postgresql.

Say we have a table "crimes" which has 2 columns "activity date"(timestamp without time zone) and "zipcode"(character varying(5)), how to query the number of crimes for each month given a zipcode?

eg: table "crimes":

activity date               zipcode
2014-11-22 00:52:00           12345
2014-10-22 00:52:00           12345
2014-10-24 00:52:00           12345
2014-12-22 00:52:00           54321

input: given zipcode"12345"

output: return 

month        count
2014-10        2
2014-11        1
2
postgresql

2 Answers

12
votes

Try:

select
    extract(year from activity_date) as year,
    to_char(activity_date, 'Mon') as month,
    count(*) as count
from
    crimes
group by
    1,extract(month from activity_date);
1
votes

How to get the month from a timestamp (with/out timezone) in PostgreSQL?

Use the function EXTRACT(field FROM source)

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

Link to documentation: https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT