Postgresql Query Between Date Ranges

Postgresql query between date ranges

With dates (and times) many things become simpler if you use >= start AND < end.

For example:

SELECT
  user_id
FROM
  user_logs
WHERE
      login_date >= '2014-02-01'
  AND login_date <  '2014-03-01'

In this case you still need to calculate the start date of the month you need, but that should be straight forward in any number of ways.

The end date is also simplified; just add exactly one month. No messing about with 28th, 30th, 31st, etc.

This structure also has the advantage of being able to maintain use of indexes.

Many people may suggest a form such as the following, but they do not use indexes:

WHERE
      DATEPART('year',  login_date) = 2014
  AND DATEPART('month', login_date) = 2

This involves calculating the conditions for every single row in the table (a scan) and not using index to find the range of rows that will match (a range-seek).

How to get data between two date range in PostgreSQL?

Your Query is absolutely fine. just change it as follows

select pi_serial, amount_in_local_currency, status, proforma_invoice_date 
from proforma_invoice
where created_by = 25 
  and to_date(proforma_invoice_date, 'DD/MM/YYYY')  BETWEEN to_date('03/01/2018', 'DD/MM/YYYY') and to_date('09/03/2018', 'DD/MM/YYYY') 
order by proforma_invoice_date

Date range to row in postgres

In Postgres, you would use generate_series():

select t1.*, gs.valid_date
from tab1 t1 cross join lateral
     generate_series(t1.open_date, t1.close_date, interval '1 day') as gs(valid_date);

Postgresql Select from date range between array of dates

An array of dates is very uncomfortable in this case. Use arrays of daterange and the containtment operator <@, e.g.:

with my_table(id, created_at) as (
values 
    (1, '2015-01-10'::timestamp),
    (2, '2016-05-10'),
    (3, '2017-10-10')
)

select *
from my_table
where created_at::date <@ any(array[
    daterange('2015-01-06','2015-02-10'), 
    daterange('2017-10-05','2017-10-11')])

 id |     created_at      
----+---------------------
  1 | 2015-01-10 00:00:00
  3 | 2017-10-10 00:00:00
(2 rows)

If you absolutely want to use an array of dates (honestly I do not think so), use this function to convert it to daterange array:

create or replace function date_pairs_to_ranges(date[])
returns daterange[] language sql as $$
    select array_agg(daterange(d1, d2))
    from unnest($1) with ordinality as u1(d1, o1)
    join unnest($1) with ordinality as u2(d2, o2)
    on o1/ 2* 2 < o1 and o2 = o1+ 1
$$;

with my_table(id, created_at) as (
values 
    (1, '2015-01-10'::timestamp),
    (2, '2016-05-10'),
    (3, '2017-10-10')
)

select *
from my_table
where created_at::date <@ any(
    date_pairs_to_ranges(array[
        '2015-01-06','2015-02-10',
        '2017-10-05','2017-10-11']::date[]))

Query between two dates and two times

select * 
from production 
where
    production.begin::date between '2017/05/15' and '2017/05/16'  
    and 
    production.begin::time between '00:15' and '15:00'

---

Related Topics