date range query help

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

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 :(

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;

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

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.

novice wrote:

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

This is the expected result given the sample data you provided. If your
sample data for call table should be 2008 instead of 2007; then output
would be:

call_id | datetime | user_id | mobile_no | charge
---------+---------------------+---------+-------------+--------
2 | 2008-10-14 13:27:00 | | 09455225998 | 5.2
1 | 2008-10-12 10:00:00 | Ben | 09455225998 | 4.5

user_id is still empty on call_id=2 because neither custodian had the
mobile on Oct.14!

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?

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

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.

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.

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

--

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

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

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

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.

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