Re: date interval - Mailing list pgsql-novice

On Thu, 5 May 2005 10:39:23 -0400, Keith Worthington wrote
> 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

Hi All,

Replying to myself.

I have figured out that I can use this expression:

        (   date_part( 'year',
                       age( date_trunc( 'month',
                                        tbl_detail.ship_by_date
                                      )
                          )
                     )::integer * 12::integer
          + date_part( 'month',
                       age( date_trunc( 'month',
                                        tbl_detail.ship_by_date
                                      )
                          )
                     )::integer
        )

instead of the original one:

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

While the first might be considered more elegant by some because it performs
the subtraction from the current date they both seem to function the same.  I
doubt that there is a performance diff between the two but I am still a
newbie.  HTH somebody someday.

Would anyone care to comment on the difference between

WHERE (expression) IN (12, 24, 36, 48)

and

WHERE (expression) BETWEEN 12 AND 48
  AND (expression) % 12 = 0

I have not been able to see a measurable difference.  Unfortunately, I am
working with a fairly small data set at this time.

Internally I believe the first WHERE clause is rewritten as

WHERE (expression) = 12
   OR (expression) = 24
   OR (expression) = 36
   OR (expression) = 48

and the second WHERE clause is rewritten as

WHERE (expression) >= 12
  AND (expression) <= 48
  AND (expression) % 12 = 0

Kind Regards,
Keith