Thread: Date Question

Hi,

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
2dates, 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
examplea 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
gettingthe 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 ;

(Please note that this query is being used in a Java application and the values shown here are examples)

Regards,

Sharon Cowling

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

Now thats something I didn't know about, thanks for that!

Regards,

Sharon Cowling



> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com]
> 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
>
>
>

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)
>
>

Sharon,

> 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

As the message says, you need to add explicit typecasts:

AND OVERLAPS ('27-06-02'::DATE, '11-07-02'::DATE, date_from, date_to)

--
-Josh Berkus