Re: date interval - Mailing list pgsql-novice

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