1

I have a tsrange as input. For example:

tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp)

I want to generate all dates belonging to this range.

I started with the following, probably naive, approach:

select generate_series(lower(r), upper(r), '1 day')::date
from   tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp) r;

This seems to work great, it gives:

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
| 2019-03-06      |
+-----------------+

However, there is are exceptions which are not handled well. Take the following range as an example:

tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp)

This is a range from 2019-03-04 00:00 inclusive to 2019-03-06 00:00 exclusive. In other words, the date 2019-03-06 does not belong to this range.

When repeating the solution above, I get the following results:

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
| 2019-03-06      |
+-----------------+

As you can see, 2019-03-06 is in the output, while it shouldn't.

I perfectly understand why this is (we asked for it in the generate_series call; it is not aware that 2019-03-06 is exclusive in our range).

A solution could be:

select generate_series(
         date_trunc('day', lower(r)),
         case when upper(r)::time = '00:00' then date_trunc('day', upper(r)) - interval '1 day'
              else                               date_trunc('day', upper(r))
         end,
         '1 day'
       )::date
from   tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp) r;

Now I get:

+-----------------+
| generate_series |
+-----------------+
| 2019-03-04      |
| 2019-03-05      |
+-----------------+

This seems to work fine. But this solution feels hackish. It also makes the assumption that the upper bound of the range is exclusive (which is the default). If the upper bound is inclusive, the original solution was fine. We can add tests on that:

select generate_series(
         date_trunc('day', lower(r)),
         case when not(upper_inc(r)) and upper(r)::time = '00:00' then date_trunc('day', upper(r)) - interval '1 day'
              else                                                     date_trunc('day', upper(r))
         end,
         '1 day'
       )::date
from   tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp, '[]') r;

Is something better possible?

1

If you move the generate_series() to the FROM clause you can use a WHERE condition to limit the rows:

select t.dt::date
from  tsrange('2019-03-04 10:00'::timestamp, '2019-03-06 20:00'::timestamp) r, 
      generate_series(lower(r), upper(r), '1 day') as t(dt)
where t.dt < case 
                when upper(r) = date_trunc('day', upper(r)) 
                     then upper(r)::date 
                else upper(r)::date + 1 
             end;
dt        
----------
2019-03-04
2019-03-05
2019-03-06

If the upper(r) is at midnight, then the generated date (t.dt) needs to be smaller than upper(r). If it's not, generated date needs to be smaller than the next day after the upper end of the range.

This query is completely independent of the hour in the actual range.

select t.dt::date
from  tsrange('2019-03-04 00:00'::timestamp, '2019-03-06 00:00'::timestamp) r, 
      generate_series(lower(r), upper(r), '1 day') as t(dt)
where t.dt < case 
                when upper(r) = date_trunc('day', upper(r)) 
                     then upper(r)::date 
                else upper(r)::date + 1 
             end;
dt        
----------
2019-03-04
2019-03-05
  • That is an interesting approach, thank you. – iwishi Mar 22 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.