Re: date interval

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

Frank