Обсуждение: Messy Casts, Is there a better way?

I have a table with the following, in part:

contract_start date
contract_term  int (term in MONTHS)

I want to calculate the contract end date.  I came up with:
CREATE FUNCTION "get_contract_end" (integer) RETURNS date AS 'SELECT
cast(contract_start + cast(cast(contract_term as text) || '' month'' as
interval) as date)
FROM circuit   WHERE internal_id = $1;' LANGUAGE 'sql';


Is there a better way? 



-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry,

> contract_start date
> contract_term  int (term in MONTHS)
> 
> I want to calculate the contract end date.  I came up with:
> CREATE FUNCTION "get_contract_end" (integer) RETURNS date AS 'SELECT
> cast(contract_start + cast(cast(contract_term as text) || '' month''
> as
> interval) as date)
> FROM circuit
>     WHERE internal_id = $1;' LANGUAGE 'sql';

Not with those data types.   Plus, there is an implicit conversion date
--> timestamp --> date in the above, which can get you in trouble.

You could use TIMESTAMP and INTERVAL instead:

contract_start TIMESTAMP WITHOUT TIME ZONE
contract_term INTERVAL

select (contract_start + contract_term) as contract_end;

Simple, neh?  The only trick is on the end of saving the data.   You
have the user input an integer, then save (using RULES or your
interface code):
"interval"(cast($term as varchar) || ' months')

This approach makes you do a little more work on the data entry end of
things, but speeds up querying considerably.   Also, should your
company policy change in the future to permit contract terms in weeks
or years, you will be ready to accomodate it.

-Josh Berkus

Larry Rosenman writes:

> I have a table with the following, in part:
>
> contract_start date
> contract_term  int (term in MONTHS)

Store contract_term as interval?

-- 
Peter Eisentraut   peter_e@gmx.net

On Tue, 2002-10-15 at 13:54, Peter Eisentraut wrote:
> Larry Rosenman writes:
> 
> > I have a table with the following, in part:
> >
> > contract_start date
> > contract_term  int (term in MONTHS)
> 
> Store contract_term as interval?
If I do that, and enter '72 months' in the contract_term field, how can
I convince PostgreSQL to output the interval back in months?  It wants
to give me '5 Years'.  

I can't seem to find a function for that.


> 
> -- 
> Peter Eisentraut   peter_e@gmx.net
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 
-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry,

> If I do that, and enter '72 months' in the contract_term field, how
> can
> I convince PostgreSQL to output the interval back in months?  It
> wants
> to give me '5 Years'.  

That's on the TODO list, I'm afraid.  From the sound of it, you should
stick with your current schema and custom function.

-Josh Berkus

On Thu, 2002-10-17 at 17:50, Josh Berkus wrote:
> Larry,
> 
> > If I do that, and enter '72 months' in the contract_term field, how
> > can
> > I convince PostgreSQL to output the interval back in months?  It
> > wants
> > to give me '5 Years'.  
> 
> That's on the TODO list, I'm afraid.  From the sound of it, you should
> stick with your current schema and custom function.
Thanks.  At least I know I'm not missing something here.  


-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749