Re: date interval

On Wed, 04 May 2005 19:18:33 -0400, Frank Bax wrote
> At 07:12 PM 5/4/05, Frank Bax wrote:
> >At 04:44 PM 5/4/05, Keith Worthington wrote:
> >>I need to generate a column representing the interval passed in months and
> >>restrict the returned data to those intervals matching 12, 24, 36 and 48.
> >>
> >>So far by reading the documentation I have gotten to these
> >>expressions. But I
> >>do not know how to get the number of months out of this.
> >>
> >> current_date - tbl_detail.ship_by_date AS elapsed_x
> >> age(tbl_detail.ship_by_date) AS elapsed_y
> >>
> >>Once I get that I am thinking that I can use the same expression with the IN
> >>to get the desired results.
> >
> >
> >date_part('epoch', age(tbl_detail.ship_by_date)) / (60*60) AS elapsed_y
> >
> >instead of "IN (12,24,36,48)" you might also consider something like
> > where elapsed_y between 12 and 48 and elapsed_y %12 = 0
> >
> >You'd have to run your own tests to see which is faster. An index might help:
> > date_part('epoch', age(tbl_detail.ship_by_date)) / (60*60)
> >or maybe (not sure if this one would get used):
> > ( date_part('epoch', age(tbl_detail.ship_by_date)) / (60*60) ) % 12
>
> Ooops, I thought you said hours - for months, something like this
> might work...
>
> ( date_part('year', xx) * 12 + date_part('month', xx) )
>

Frank,

Thanks for the post. Using your suggestion I have built the following query.
It works just fine. I am thinking about building a function to store the
repetitious part of the code. Mainly because, well, its ugly. I was
originally going off in another direction trying to find an elegant way to do
this using built in date functions. But hey, working ugly beats broke elegant
any day! :-)

SELECT tbl_detail.so_number,
tbl_detail.order_date,
tbl_detail.ship_by_date,
( ( date_part('year', current_date)::integer * 12::integer
+ date_part('month', current_date)::integer
) -
( date_part('year', tbl_detail.ship_by_date)::integer * 12::integer
+ date_part('month', tbl_detail.ship_by_date)::integer
)
) AS age_in_months
FROM tbl_detail
WHERE ( ( date_part('year', current_date)::integer * 12::integer
+ date_part('month', current_date)::integer
) -
( date_part('year', tbl_detail.ship_by_date)::integer * 12::integer
+ date_part('month', tbl_detail.ship_by_date)::integer
)
) BETWEEN 12 AND 48
AND ( ( date_part('year', current_date)::integer * 12::integer
+ date_part('month', current_date)::integer
) -
( date_part('year', tbl_detail.ship_by_date)::integer * 12::integer
+ date_part('month', tbl_detail.ship_by_date)::integer
)
) % 12::integer = 0
ORDER BY tbl_detail.so_number;

Kind Regards,
Keith