Thread: How to search using daterange (using gist)
Hi All,
please help
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?
thanks
win
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