From: Josh Berkus <josh@agliodbs.com>
To: Larry Rosenman <ler@lerctr.org>
To: pgsql-sql@postgresql.org
Subject: Re: Messy Casts, Is there a better way?
Date: Tue, 15 Oct 2002 09:28:12 -0700
Message-ID: <web-1787228@davinci.ethosmedia.com> (raw)
In-Reply-To: <1034676979.436.1.camel@lerlaptop.lerctr.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, ler@lerctr.org
Subject: Re: Messy Casts, Is there a better way?
In-Reply-To: <web-1787228@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox