Re: Date Question - Mailing list pgsql-novice

Josh,

I'm getting an error when I run that overlaps function.

taupo=> SELECT permit_id
taupo-> FROM faps_permit
taupo-> WHERE person_id = (select person_id from person where person_id = 100)
taupo-> AND cancel_permit is null
taupo-> AND location = 'Kent Forest'
taupo-> AND OVERLAPS ('27-06-02', '11-07-02', date_from, date_to)
taupo-> ORDER BY permit_id ;
ERROR:  Function 'overlaps(unknown, unknown, date, date)' does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts

Regards,

Sharon Cowling



> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Josh Berkus
> Sent: Thursday, 27 June 2002 15:56
> To: Sharon Cowling; Pgsql-Novice (E-mail)
> Subject: Re: [NOVICE] Date Question
>
>
>
> Sharon,
>
> > I have a query that is not returning the desired results.
> I want the
> results to be a list of permit_id's held between 2 dates, but
> the results I'm
> getting is only those permit_id's that start on date_from
> ('27-06-02') and
> finish on date_to ('11-07-02'), I also want the permit_id's
> returned for
> those permits that are held within that time period, for
> example a permit
> that starts on 20-06-02 and finishes on 04-07-02.  I've tried
> all sorts of
> variations but I'm still not getting the right results,
> anyone have any
> ideas?
> >
> > SELECT permit_id
> > FROM faps_permit
> > WHERE person_id = (select person_id from person where
> person_id = 100)
> > AND cancel_permit is null
> > AND location = 'Kent Forest'
> > AND ( (date_from >= '27-06-02' AND date_to <= '11-07-02')
> > OR (date_from <= '27-06-02' AND date_to >= '11-07-02') )
> > ORDER BY permit_id ;
>
> According to the conditions above, you'll be getting any
> permit_id where from
> and to are both between the two dates, or from and to are
> both outside the
> two dates.  Not what you want.
>
> Actually, what you want is the OVERLAPS function:
>
> SELECT permit_id
> FROM faps_permit
> WHERE person_id = (select person_id from person where person_id = 100)
> AND cancel_permit is null
> AND location = 'Kent Forest'
> AND OVERLAPS ('27-06-02, '11-07-02', date_from, date_to)
> ORDER BY permit_id ;
>
> This will give you all permits that started, ended or were
> held during the
> named period.
>
> -Josh Berkus
>
>
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>