date range query help - Mailing list pgsql-sql

From novice
Subject date range query help
Date
Msg-id ddcb1c340811191810k6312f02fm9bbc28a4b94b5299@mail.gmail.com
Whole thread Raw
List pgsql-sql
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.

pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Query to retrieve all indexed columns
Next
From: novice
Date:
Subject: Re: [GENERAL] date range query help