Обсуждение: date range query help
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.
> 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.
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_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
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