Group records from two dates in PostgreSQL

Question

Given the following table structure below, I have the following problem:

                                    Table "public.search"

 Column           |            Type             |                       Modifiers                       
------------------+-----------------------------+-------------------------------------------------------
 id               | bigint                      | not null default nextval('search_id_seq'::regclass)
 date             | timestamp without time zone | 
 datesolicitation | timestamp without time zone | 
 reason           | character varying(255)      | 
 notice           | real                        | not null
 situation        | character(1)                | not null
 urgent           | boolean                     | not null
 expiring         | timestamp without time zone | 
 bond             | character(1)                | not null
 company_id       | bigint                      | 
 vehicle_id       | bigint                      | 
 driver_id        | bigint                      | 
 locality_id      | bigint                      | 
 set_id           | bigint                      | 
 composition_id   | bigint                      | 
 responsible_id   | bigint                      | 
 fleet            | character varying(255)      |

Every day I run a query that returns all the records to expire in the next five days based on the table expiring column:

SELECT
  COUNT(id) AS total
FROM search 
WHERE
  bond IN ('F', 'A', 'G', 'J')
  AND expiring - interval '5 day' between '2017-09-25 12:00:00' and '2017-09-26 12:00:00';

 total 
-------
   504
(1 row)

The problem occurs when the query in question is executed for another day or when we have more than one "base" date for return.

SELECT
  COUNT(DISTINCT id) AS total
  TO_CHAR(expiring, 'DD/MM/YYYY')
FROM search 
WHERE
  vinculo IN ('F', 'A', 'G', 'J')
  AND expiring - interval '5 day' between '2017-09-25 12:00:00' AND '2017-09-28 12:00:00'
GROUP BY expiring;

 total |  to_char   
-------+------------
     1 | 30/09/2017
     1 | 30/09/2017
     1 | 30/09/2017
     1 | 30/09/2017
     1 | 30/09/2017
     1 | 30/09/2017

Since the consultation period is from 12:00:00 a day until 12:00:00 on another or that is on September 25 as presented in the query will also be considered all records from September 26 to 12 : 00: 00. On September 26 will also be considered the records of September 27 successively. Does anyone have any ideas?

Thank you!

Something like group by date_trunc('day', expiring - '11:59:59'::time)? — Abelisto
– Abelisto, Commented Sep 21, 2017 at 20:23

Juan Carlos Oropeza · Accepted Answer · 2017-09-21 20:13:04Z

1

what about instead you do:

WHERE expiring BETWEEN CURRENT_TIMESTAMP 
                   AND CURRENT_TIMESTAMP + INTERVAL '5 days'
GROUP BY TO_CHAR(expiring, 'DD/MM/YYYY');

answered Sep 21, 2017 at 20:13

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

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

Collectives™ on Stack Overflow

Group records from two dates in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related