Postgres query including time range

Question

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!

Answer 1

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