CODEWITHSUNDEEP
sqlpostgresql
Toshi
Toshi

Reputation: 6342

trim the time out of the range

I use PostgreSQL, and I'd love to trim the date if it's out of the range.

First thing I'd love to filter the records out of the range. It's not difficult with query below. (Filter from 2017/3/1 ~ 2017/3/31)

select * from subscriptions where current_period_start_at <= date '2017/03/01' AND current_period_end_at >= date '2017/03/31'

However I'd love to cut off the date if it's out of the range(2017-03-1 ~ 2017-03-31). current_period_start_at: 2017/02/25 => 2017/03/01 current_period_end_at: 2017/04/02 => 2017/03/31

Here is some example.

enter image description here

target　2016/08/05 ～ 2016/09/15　
range①　　2016/07/01 ～ 2016/08/31 => 2016/08/05 ～ 2016/08/31
range②　　2016/08/10 ～ 2016/09/20 => 2016/08/10 ～ 2016/09/15
range③　　2016/09/15 ～ 2016/10/10 => x out of the scope
range④　　2016/08/01 ～ 2016/09/30 => 2016/08/05 ～ 2016/09/15

Upvotes: 3

Views: 69

Answers (2)

user330315
user330315

Reputation:

You can use the least() function to get the lower of the values:

select  greatest(current_period_start_at, date '2016-08-05') as range_start, 
        least(current_period_end_at, date '2016-09-15') as range_end
from subscriptions
where (current_period_start_at,current_period_end_at) 
         overlaps (date '2016-08-05', date '2016-09-15');

If you don't want to repeat the date value, you can use a common table expression:

with range (r_start, r_end) as (
  values (date '2016-08-05', date '2016-09-15')
)
select greatest(current_period_start_at, r_start) as range_start,
       least(current_period_end_at, r_end) as range_end
from subscriptions, range
where (current_period_start_at,current_period_end_at) 
      overlaps (r_start, r_end);

Upvotes: 4

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

You should try daterange datatype.

SELECT daterange('2017-01-01'::date, '2017-03-01'::date) * daterange('2017-02-15'::date, '2017-05-01'::date);

returns

[2017-02-15,2017-03-01)

Upvotes: 3

Related Questions