Re: Date Question

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@xxxxxxxxxxxxxx
> [mailto:pgsql-novice-owner@xxxxxxxxxxxxxx]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@xxxxxxxxxxxxxx)
> 
>




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)