Getting Date List in a Range in Postgresql

Getting date list in a range in PostgreSQL

select CURRENT_DATE + i 
from generate_series(date '2012-06-29'- CURRENT_DATE, 
     date '2012-07-03' - CURRENT_DATE ) i

or even shorter:

select i::date from generate_series('2012-06-29', 
  '2012-07-03', '1 day'::interval) i

postgresql generating a list of dates between two dates fields

Use generate_series()

select t.id, t.name, t.g.dt::date as start_end
from the_table t
  cross join generate_series(t.date_start, t.date_end, interval '1 day') as g(dt)
order by t.id, g.dt;

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).

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);

Generating time series between two dates in PostgreSQL

Can be done without conversion to/from int (but to/from timestamp instead)

SELECT date_trunc('day', dd):: date
FROM generate_series
        ( '2007-02-01'::timestamp 
        , '2008-04-01'::timestamp
        , '1 day'::interval) dd
        ;

How to get unique date range in PostgreSQL?

I found a way, but don't know whether or not it is a best way.

Here is my solution:

select 
  distinct generate_series 
from 
  results, 
  generate_series(results.start_dte, results.end_dte, '1 day'::interval) 
order by generate_series asc;

output:

    generate_series
------------------------
 2018-08-01 00:00:00+00
 2018-08-02 00:00:00+00
 2018-08-03 00:00:00+00
 2018-08-04 00:00:00+00
 2018-08-05 00:00:00+00
 2018-08-09 00:00:00+00
 2018-08-10 00:00:00+00
(7 rows)

Get days of the week from a date range in Postgres

You can use the * operator with tsranges, generate a series of dates with the lower and upper dates and finally with to_char print the days of the week, e.g.

SELECT 
  id, name, start_date, end_date, array_agg(dow) AS days
FROM (
  SELECT *,  
    trim(
      to_char(
        generate_series(lower(overlap), upper(overlap),'1 day'),
      'Day')) AS dow
  FROM holidays
  CROSS JOIN LATERAL (SELECT tsrange(start_date,end_date) * 
                             tsrange('2022-07-18', '2022-07-26')) t (overlap)
  WHERE tsrange(start_date,end_date) && tsrange('2022-07-18', '2022-07-26')) j
GROUP BY id,name,start_date,end_date,number_of_days;

 id |   name   | start_date |  end_date  |            days            
----+----------+------------+------------+----------------------------
  6 | holiday6 | 2022-07-12 | 2022-07-20 | {Monday,Tuesday,Wednesday}
(1 row)

Demo: db<>fiddle

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[]))

Gaps and Islands - get a list of dates unemployed over a date range with Postgresl

• first you need to find what dates overlaps Determine Whether Two Date Ranges Overlap
• then merge those ranges as a single one and keep the last id
• finally calculate the ranges of days between one end_date and the next start_date - 1

SQL DEMO

with find_overlap as (
  SELECT t1."id" as t1_id, t1."person_id", t1."start_date", t1."end_date",
         t2."id" as t2_id, t2."start_date" as t2_start_date, t2."end_date" as t2_end_date
  FROM Table1 t1
  LEFT JOIN Table1 t2
    ON t1."person_id" = t2."person_id"
   AND t1."start_date" <= t2."end_date"
   AND t1."end_date"   >= t2."start_date"
   AND t1.id < t2.id
), merge_overlap as (
  SELECT 
         person_id,
         start_date,
         COALESCE(t2_end_date, end_date) as  end_date,
         COALESCE(t2_id, t1_id) as last_position_id
  FROM find_overlap
  WHERE t1_id NOT IN (SELECT t2_id FROM find_overlap WHERE t2_ID IS NOT NULL)
), cte as (
  SELECT *, 
         LEAD(start_date) OVER (partition by person_id order by start_date) next_start
  FROM merge_overlap 
) 
SELECT *, 
       DATE_PART('day', 
                  (next_start::timestamp - INTERVAL '1 DAY') - end_date::timestamp
                ) as days 
FROM cte
WHERE next_start IS NOT NULL 

OUTPUT

| person_id | start_date |   end_date | last_position_id | next_start | days |
|-----------|------------|------------|------------------|------------|------|
|         1 | 2001-12-01 | 2002-01-31 |                1 | 2002-02-11 |   10 |
|         1 | 2002-02-11 | 2002-05-31 |                3 | 2002-06-15 |   14 |

---

Related Topics