From: | Andreas Kretschmer <andreas(at)a-kretschmer(dot)de> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to search using daterange (using gist) |
Date: | 2019-05-16 10:16:08 |
Message-ID: | fff9dffb-4eae-8aee-4a91-d26e19ba8326@a-kretschmer.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Am 16.05.19 um 11:57 schrieb Winanjaya Amijoyo:
> Hi All,
>
> I have records as below that I inserted using exclusion gist constraint
>
> user_id start_date end_date pid
> 001 2019-01-01 2019-02-10 1
> 001 2019-02-01 2019-03-12 2
> 001 2019-03-05 2019-06-15 3
>
> I need to find records based on date range, so for example:
>
> if I search using parameters 001, 2019-01-10, 2019-02-11 .. it will
> found as pid 1
> if I search using parameters 001, 2019-03-10, 2019-07-13 .. it will
> found as pid 3
> but if I search out of the date range, for example:
> if I search using parameters 001, 2019-07-10, 2019-09-13 .. it will
> not found as pid 0
>
> how to search with the above scenario in postgresql?
>
>
can you please show us the table-definition? Are you sure there is an
exclusion constraint?
with your data:
test=*# select * from demo;
user_id | start_date | end_date | pid
---------+------------+------------+-----
1 | 2019-01-01 | 2019-02-10 | 1
1 | 2019-02-01 | 2019-03-12 | 2
1 | 2019-03-05 | 2019-06-15 | 3
(3 rows)
test=*# select * from demo where daterange(start_date, end_date,'[)') &&
daterange('2019-01-10','2019-02-11');
user_id | start_date | end_date | pid
---------+------------+------------+-----
1 | 2019-01-01 | 2019-02-10 | 1
1 | 2019-02-01 | 2019-03-12 | 2
(2 rows)
test=*# select * from demo where daterange(start_date, end_date,'[)') &&
daterange('2019-03-10','2019-07-13');
user_id | start_date | end_date | pid
---------+------------+------------+-----
1 | 2019-02-01 | 2019-03-12 | 2
1 | 2019-03-05 | 2019-06-15 | 3
(2 rows)
test=*# select * from demo where daterange(start_date, end_date,'[)') &&
daterange('2019-07-10','2019-09-13');
user_id | start_date | end_date | pid
---------+------------+----------+-----
(0 rows)
test=*#
test=*# \d demo
Table "public.demo"
Column | Type | Collation | Nullable | Default
------------+---------+-----------+----------+---------
user_id | integer | | |
start_date | date | | |
end_date | date | | |
pid | integer | | |
test=*#
Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
From | Date | Subject | |
---|---|---|---|
Next Message | Winanjaya Amijoyo | 2019-05-16 14:10:51 | Re: Returning empty on insert |
Previous Message | Winanjaya Amijoyo | 2019-05-16 09:57:05 | How to search using daterange (using gist) |