How to search using daterange (using gist)

From: Winanjaya Amijoyo <winanjaya(dot)amijoyo(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: How to search using daterange (using gist)
Date: 2019-05-16 09:57:05
Message-ID: CAAHo4LPCQe=0vmDrOGdpitm+46aN_uW=hp9Rd=boBkvtn6Ymrg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

please help

thanks
win

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2019-05-16 10:16:08 Re: How to search using daterange (using gist)
Previous Message nigel.andersen 2019-05-16 09:36:56 Upgrading 9.1.17 to which version?