Skip site navigation (1) Skip section navigation (2)

date range query help

From: novice <user(dot)postgresql(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: date range query help
Date: 2008-11-20 02:10:41
Message-ID: ddcb1c340811191810k6312f02fm9bbc28a4b94b5299@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-novicepgsql-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.

Responses

pgsql-novice by date

Next:From: Adam RichDate: 2008-11-20 02:42:51
Subject: Re: date range query help
Previous:From: Obe, ReginaDate: 2008-11-19 22:49:51
Subject: Re: ssl tunneling in postgres 8.1

pgsql-general by date

Next:From: Adam RichDate: 2008-11-20 02:42:51
Subject: Re: date range query help
Previous:From: Koichi SuzukiDate: 2008-11-20 00:04:28
Subject: Re: lesslog "incorrect resource manager data checksum."

pgsql-sql by date

Next:From: Adam RichDate: 2008-11-20 02:42:51
Subject: Re: date range query help
Previous:From: Tom LaneDate: 2008-11-19 18:24:26
Subject: Re: Query to retrieve all indexed columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group