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!

Kamil Gosciminski · Accepted Answer · 2016-09-21 22:50:48Z

7

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

edited Sep 21, 2016 at 22:50

answered Sep 21, 2016 at 21:25

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

donkopotamus Over a year ago

The original question is using a range of the form date >= X and date < Y so the BETWEEN operator isn't quite appropriate in this case.

Cetin Basoz Over a year ago

I think it would be < '2016/09/05' otherwise it might select items from '2016/09/05' too, no? IMHO between operator should not be used for ranges that may have time component (what is the upper limit?).

Cetin Basoz Over a year ago

No they wouldn't be discarded. Where does it says that they would?

quanta Over a year ago

What happens when the timestamp also includes time? e.g., "2020-03-01 00:13:01", and we also would like to select time range not just date?

Kamil Gosciminski Over a year ago

You need to include specific timestamps, or if the date and time are not related, then you will need to use two separate conditions and use conjunction.

|

Cetin Basoz · Accepted Answer · 2020-05-18 11:48:59Z

2

You were close, you need to supply the column name on second part of where too and you would have a single where:

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

EDIT: This part is just for Kamil G. showing clearly that between should NOT be used:

create table sample (id int, d timestamp);

insert into sample (id, d) 
values 
(1, '2016/09/01'),
(2, '2016/09/02'),
(3, '2016/09/03'),
(4, '2016/09/04'),
(5, '2016/09/05'),
(6, '2016/09/05 00:00:00'),
(7, '2016/09/05 00:00:01'),
(8, '2016/09/06');

select * from sample where d between '2016-09-01' and '2016-09-05';

Result:

1;"2016-09-01 00:00:00"
2;"2016-09-02 00:00:00"
3;"2016-09-03 00:00:00"
4;"2016-09-04 00:00:00"
5;"2016-09-05 00:00:00"
6;"2016-09-05 00:00:00"

BTW if you wouldn't believe without seeing explain, then here it is:

Filter: ((d >= '2016-09-01 00:00:00'::timestamp without time zone) AND (d <= '2016-09-05 00:00:00'::timestamp without time zone))

edited May 18, 2020 at 11:48

answered Sep 21, 2016 at 22:36

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

5 Comments

quanta Over a year ago

Strangely nobody says anything about a specific time range, just data and 00:00:00, it's unbelievable that something like between "2019-09-11 00:12:01" and "2019-09-11 20:12:01" wouldn't exist!

Cetin Basoz Over a year ago

@quanta, I am sorry I don't understand what you mean. If you are saying you need to have time part too in a range query, then you shouldn't use BETWEEN in the first place. BETWEEN implicitly means dtCol >= startVal AND dtCol <= endVal. When time is included, there is no way to specify the endVal. You should change your query to read dtCol >= startVal AND dtCol < endVal where endVal is the minimum value that you want to exclude (ie: May 2020 would be 2020-05-01 AND 2020-06-01). Probably you didn't understand the samples above showing why BETWEEN should NOT be used.

quanta Over a year ago

I understood your point, I was just referring to the fact that time range wasn't covered in any of the answers, here and in most threads. I needed the time part and found this thread based on the title that said time range but couldn't find a straight-forward answer. I took bits and pieces and found this to be working for me:

select whatever_column FROM  some_table WHERE time_column > '2020-03-01 00:00:01'::TIMESTAMP AND time_column < '2020-03-01 01:13:01'::TIMESTAMP

Cetin Basoz Over a year ago

@quanta, there is a straight-forward answer above. What you have written is already there in the code at top.

quanta Over a year ago

I didn't see any specific example with time, all examples had only date in their query and in some case 00:00:00.00000, a straightforward example with times other than 00:00:00.00000 would be very helpful for an absolute newbie though. But thanks.

Collectives™ on Stack Overflow

Postgres query including time range

2 Answers 2

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related