Thread: date range query help

date range query help

From
novice
Date:
Hi,

We have two tables.

select * from
mobile_custodian;

 custodian_id | user_id | issue_date | return_date |  mobile_no
--------------+---------+------------+-------------+-------------
            4 |     Ben | 2008-10-11 | 2008-10-13  | 09455225998
            5 |    Josh | 2008-10-15 |             | 09455225998
(2 rows)


select * from
call;

 call_id |      datetime       |  mobile_no  | charge
---------+---------------------+-------------+--------
       2 | 2007-10-14 13:27:00 | 09455225998 |    5.2
       1 | 2007-10-12 10:00:00 | 09455225998 |    4.5
(2 rows)



Now user Ben has passed his mobile to user Josh and we issued Josh his
mobile on 2008-10-15.

1. Is it possible for me to write a query that will have the fields

call.call_id,
call.datetime,
mobile_custodian.user_id,
call.mobile_no
call.charge

that will use call.datetime and lookup the date range from
mobile_custodian.issue_date and mobile_custodian.return_date to
identify the right user for each call?

2.  Do I need to change the issue_date & return_date fields to
timestamp to perform the above?


Thanks.

Re: date range query help

From
"Adam Rich"
Date:
> Now user Ben has passed his mobile to user Josh and we issued Josh his
> mobile on 2008-10-15.
>
> 1. Is it possible for me to write a query that will have the fields
>
> call.call_id,
> call.datetime,
> mobile_custodian.user_id,
> call.mobile_no
> call.charge
>
> that will use call.datetime and lookup the date range from
> mobile_custodian.issue_date and mobile_custodian.return_date to
> identify the right user for each call?
>
> 2.  Do I need to change the issue_date & return_date fields to
> timestamp to perform the above?
>

No, a date will work fine.  Try this:


select call.call_id,
 call.datetime,
 mobile_custodian.user_id,
 call.mobile_no
 call.charge
from call, mobile_custodian
where call.mobile_no = mobile_custodian.mobile_no
and call.datetime between mobile_custodian.issue_date
    and mobile_custodian.return_date



Re: date range query help

From
novice
Date:
2008/11/20 Adam Rich <adam.r@sbcglobal.net>:
>> Now user Ben has passed his mobile to user Josh and we issued Josh his
>> mobile on 2008-10-15.
>>
>> 1. Is it possible for me to write a query that will have the fields
>>
>> call.call_id,
>> call.datetime,
>> mobile_custodian.user_id,
>> call.mobile_no
>> call.charge
>>
>> that will use call.datetime and lookup the date range from
>> mobile_custodian.issue_date and mobile_custodian.return_date to
>> identify the right user for each call?
>>
>> 2.  Do I need to change the issue_date & return_date fields to
>> timestamp to perform the above?
>>
>
> No, a date will work fine.  Try this:
>
>
> select call.call_id,
>  call.datetime,
>  mobile_custodian.user_id,
>  call.mobile_no
>  call.charge
> from call, mobile_custodian
> where call.mobile_no = mobile_custodian.mobile_no
> and call.datetime between mobile_custodian.issue_date
>        and mobile_custodian.return_date

sorry I get nothing :(

Re: date range query help

From
brian
Date:
novice wrote:
> 2008/11/20 Adam Rich <adam.r@sbcglobal.net>:
>>
>> select call.call_id,
>>  call.datetime,
>>  mobile_custodian.user_id,
>>  call.mobile_no
>>  call.charge
>> from call, mobile_custodian
>> where call.mobile_no = mobile_custodian.mobile_no
>> and call.datetime between mobile_custodian.issue_date
>>        and mobile_custodian.return_date
>
> sorry I get nothing :(
>

How about:

SELECT call.call_id,
call.datetime,
mobile_custodian.user_id,
call.mobile_no,
call.charge
FROM call
LEFT JOIN mobile_custodian
ON call.mobile_no = mobile_custodian.mobile_no
AND call.datetime
   BETWEEN
   mobile_custodian.issue_date
   AND
   mobile_custodian.return_date;

Re: date range query help

From
novice
Date:
2008/11/20 brian <brian@zijn-digital.com>:
> novice wrote:
>>
>> 2008/11/20 Adam Rich <adam.r@sbcglobal.net>:
>>>
>>> select call.call_id,
>>>  call.datetime,
>>>  mobile_custodian.user_id,
>>>  call.mobile_no
>>>  call.charge
>>> from call, mobile_custodian
>>> where call.mobile_no = mobile_custodian.mobile_no
>>> and call.datetime between mobile_custodian.issue_date
>>>       and mobile_custodian.return_date
>>
>> sorry I get nothing :(
>>
>
> How about:
>
> SELECT call.call_id,
> call.datetime,
> mobile_custodian.user_id,
> call.mobile_no,
> call.charge
> FROM call
> LEFT JOIN mobile_custodian
> ON call.mobile_no = mobile_custodian.mobile_no
> AND call.datetime
>  BETWEEN
>  mobile_custodian.issue_date
>  AND
>  mobile_custodian.return_date;
>

this gave me nothing on the user_id field  :(

 call_id |      datetime       | user_id |  mobile_no  | charge
---------+---------------------+---------+-------------+--------
       1 | 2007-10-12 10:00:00 |         | 09455225998 |    4.5
       2 | 2007-10-16 13:27:00 |         | 09455225998 |    5.2

Re: [SQL] date range query help

From
"Rodrigo E. De León Plicet"
Date:
On Wed, Nov 19, 2008 at 10:03 PM, novice <user.postgresql@gmail.com> wrote:
> sorry I get nothing :(

Of course not. None of the dates you gave in the example overlap.

Re: [SQL] date range query help

From
novice
Date:
2008/11/20 Rodrigo E. De León Plicet <rdeleonp@gmail.com>:
> On Wed, Nov 19, 2008 at 10:03 PM, novice <user.postgresql@gmail.com> wrote:
>> sorry I get nothing :(
>
> Of course not. None of the dates you gave in the example overlap.
>

But it should still have the 1st entry with the name Ben?  Am I
missing something?

Re: [SQL] date range query help

From
brian
Date:
novice wrote:
> 2008/11/20 Rodrigo E. De León Plicet <rdeleonp@gmail.com>:
>> On Wed, Nov 19, 2008 at 10:03 PM, novice <user.postgresql@gmail.com> wrote:
>>> sorry I get nothing :(
>> Of course not. None of the dates you gave in the example overlap.
>>
>
> But it should still have the 1st entry with the name Ben?  Am I
> missing something?
>

2007 -> 2008

Re: [SQL] date range query help

From
novice
Date:
omg - how embarrassing.
so sorry :(

2008/11/20 brian <brian@zijn-digital.com>:
> novice wrote:
>>
>> 2008/11/20 Rodrigo E. De León Plicet <rdeleonp@gmail.com>:
>>>
>>> On Wed, Nov 19, 2008 at 10:03 PM, novice <user.postgresql@gmail.com>
>>> wrote:
>>>>
>>>> sorry I get nothing :(
>>>
>>> Of course not. None of the dates you gave in the example overlap.
>>>
>>
>> But it should still have the 1st entry with the name Ben?  Am I
>> missing something?
>>
>
> 2007 -> 2008
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this.

Re: [SQL] date range query help

From
"Adam Rich"
Date:
> >> sorry I get nothing :(
> >
> > Of course not. None of the dates you gave in the example overlap.
> >
>
> But it should still have the 1st entry with the name Ben?  Am I
> missing something?

Ben's issue dates are in the year 2008.  The first call entry is
in the year 2007.   There are no custodians with a matching issue
date.






Re: [SQL] date range query help

From
"Joshua D. Drake"
Date:
On Thu, 2008-11-20 at 15:41 +1100, novice wrote:
> omg - how embarrassing.
> so sorry :(
>

I note your name is novice.

Don't sweat it. We are here to help.

Joshua D. Drake

--


Re: date range query help

From
"Raymond C. Rodgers"
Date:
Adam Rich wrote:
Now user Ben has passed his mobile to user Josh and we issued Josh his
mobile on 2008-10-15.

1. Is it possible for me to write a query that will have the fields

call.call_id,
call.datetime,
mobile_custodian.user_id,
call.mobile_no
call.charge

that will use call.datetime and lookup the date range from
mobile_custodian.issue_date and mobile_custodian.return_date to
identify the right user for each call?

2.  Do I need to change the issue_date & return_date fields to
timestamp to perform the above?
   
No, a date will work fine.  Try this:


select call.call_id,call.datetime,mobile_custodian.user_id,call.mobile_nocall.charge
from call, mobile_custodian
where call.mobile_no = mobile_custodian.mobile_no
and call.datetime between mobile_custodian.issue_dateand mobile_custodian.return_date

 
Here's my version of the query which takes into account calls that are made with the current custodian, therefore the return_date field is null (sorry for the lack of formatting):


select c.call_id, c.datetime, mc.user_id, c.mobile_no, c.charge from mobile_custodian mc
 inner join call c using (mobile_no) where (c.datetime between mc.issue_date and
 mc.return_date) or (c.datetime > mc.issue_date and mc.return_date is null)

Raymond

Re: date range query help

From
Sam Mason
Date:
On Thu, Nov 20, 2008 at 01:10:41PM +1100, novice wrote:
> select * from mobile_custodian;
>
>  custodian_id | user_id | issue_date | return_date |  mobile_no
> --------------+---------+------------+-------------+-------------
>             4 |     Ben | 2008-10-11 | 2008-10-13  | 09455225998
>             5 |    Josh | 2008-10-15 |             | 09455225998

I sometimes find it easier to store ranges like this with having the
open ends at infinity.  This is easier with timestamps as they have
magic 'infinity' values in PG (both positive and negative).  Values of
date type don't have any special values like this which makes things a
bit more awkward.

I haven't seen it posted to the list for a while; but there's a nice
old book titled "Developing Time Oriented Database Applications in SQL"
that's now out of print but is available as a PDF.

  http://www.cs.arizona.edu/~rts/tdbbook.pdf


  Sam

Re: [SQL] date range query help

From
Frank Bax
Date:
novice wrote:
> 2008/11/20 Rodrigo E. De León Plicet <rdeleonp@gmail.com>:
>> On Wed, Nov 19, 2008 at 10:03 PM, novice <user.postgresql@gmail.com> wrote:
>>> sorry I get nothing :(
>> Of course not. None of the dates you gave in the example overlap.
>>
>
> But it should still have the 1st entry with the name Ben?  Am I
> missing something?
>



Yes, you are missing something.  You would only get 1st entry with the
name Ben if the dates in two tables were in the same year.

Re: date range query help

From
Tom Lane
Date:
Sam Mason <sam@samason.me.uk> writes:
> I sometimes find it easier to store ranges like this with having the
> open ends at infinity.  This is easier with timestamps as they have
> magic 'infinity' values in PG (both positive and negative).  Values of
> date type don't have any special values like this which makes things a
> bit more awkward.

Just FYI, date does have +/-infinity as of 8.4.

            regards, tom lane