Postgres query including time range

I have a query that pulls part of the data that I need for tracking. What I need to add is either a column that includes the date or the ability to query the table for a date range. I would prefer the column if possible. I am using psql 8.3.3.

Here is the current query:

select count(mailing.mailing_id) as mailing_count, department.name as org 
from mailing, department 
where mailing.department_id = department.department_id 
group by department.name;

This returns the following information:

mailing_count  |                   org                   
---------------+-----------------------------------------
             2 | org1 name
             8 | org2 name
            22 | org3 name
            21 | org4 name
            39 | org5 name

The table that I am querying has 3 columns that have date in a timestamp format which are target_launch_date, created_time and modified_time.

When I try to add the date range to the query I get an error:

Query:

select count(mailing.mailing_id) as mailing_count, department.name as org 
from mailing, department 
where mailing.department_id = department.department_id 
group by department.name,
WHERE (target_launch_date)>= 2016-09-01 AND < 2016-09-05;

Error:

ERROR: syntax error at or near "WHERE" LINE 1: ...department.department_id group by department.name,WHERE(targ...

I've tried moving the location of the date range in the string and a variety of other changes, but cannot achieve what I am looking for.

Any insight would be greatly appreciated!

Solution

Here's a query that would do what you need:

SELECT 
  count(m.mailing_id) as mailing_count, 
  d.name as org 
FROM mailing m
JOIN department d USING( department_id )
WHERE 
  m.target_launch_date BETWEEN '2016-09-01' AND '2016-09-05'
GROUP BY 2

Since your target_launch_date is of type timestamp you can safely do <= '2016-09-05' which will actually convert to 2016-09-05 00:00:00.00000 giving you all the dates that are before start of that day or exactly 2016-09-05 00:00:00.00000

Couple of additional notes:

Use aliases for table names to shorten the code, eg. mailing m
Use explicit JOIN syntax to connect data from related tables
Apply your WHERE clause before GROUP BY to exclude rows that don't match it
Use BETWEEN operator to handle date >= X AND date <= Y case
You can use USING instead of ON in JOIN syntax when joined column names are the same
You can use column numbers in GROUP BY which point to position of a column in your select

To gain more insight on the matter of how processing of a SELECT statement behaves in steps look at the documentation.

Edit

Approach using BETWEEN operator would account 2015-09-05 00:00:00.00000 to the resultset. If this timestamp should be discarded change BETWEEN x AND y to either of those two:

(...) BETWEEN x AND y::timestamp - INTERVAL '1 microsecond'
(...) >= x AND (...) < y