Thread: date interval
Hi All,
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.
Any help on the expressions would be appreciated.
Kind Regards,
Keith
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
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) )
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
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