Home > mailing lists

How to search using daterange (using gist) - Mailing list pgsql-general

From	Winanjaya Amijoyo
Subject	How to search using daterange (using gist)
Date	May 16, 2019 12:57:05
Msg-id	CAAHo4LPCQe=0vmDrOGdpitm+46aN_uW=hp9Rd=boBkvtn6Ymrg@mail.gmail.com Whole thread Raw
Responses	Re: How to search using daterange (using gist)
List	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

From: David Rowley
Date: 16 May 2019, 12:25:28
Subject: Re: Returning empty on insert

From: Andreas Kretschmer
Date: 16 May 2019, 13:16:08
Subject: Re: How to search using daterange (using gist)