From: | Frank Bax <fbax(at)sympatico(dot)ca> |
---|---|
To: | "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: date interval |
Date: | 2005-05-04 23:18:33 |
Message-ID: | 5.2.1.1.0.20050504191625.03b71640@pop6.sympatico.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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) )
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2005-05-04 23:34:06 | Re: connect from perl: error in Carp.pm |
Previous Message | Frank Bax | 2005-05-04 23:12:17 | Re: date interval |