pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMessy Casts, Is there a better way?
6+ messages / 3 participants
[nested] [flat]
* Messy Casts, Is there a better way?
@ 2002-10-15 10:16 Larry Rosenman <ler@lerctr.org>
0 siblings, 2 replies; 6+ messages in thread
From: Larry Rosenman @ 2002-10-15 10:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Messy Casts, Is there a better way?
@ 2002-10-15 16:28 Josh Berkus <josh@agliodbs.com>
parent: Larry Rosenman <ler@lerctr.org>
1 sibling, 0 replies; 6+ messages in thread
From: Josh Berkus @ 2002-10-15 16:28 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Messy Casts, Is there a better way?
@ 2002-10-15 18:54 Peter Eisentraut <peter_e@gmx.net>
parent: Larry Rosenman <ler@lerctr.org>
1 sibling, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2002-10-15 18:54 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Messy Casts, Is there a better way?
@ 2002-10-17 20:22 Larry Rosenman <ler@lerctr.org>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: Larry Rosenman @ 2002-10-17 20:22 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Messy Casts, Is there a better way?
@ 2002-10-17 22:50 Josh Berkus <josh@agliodbs.com>
parent: Larry Rosenman <ler@lerctr.org>
0 siblings, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2002-10-17 22:50 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Messy Casts, Is there a better way?
@ 2002-10-18 00:13 Larry Rosenman <ler@lerctr.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Larry Rosenman @ 2002-10-18 00:13 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-10-18 00:13 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-15 10:16 Messy Casts, Is there a better way? Larry Rosenman <ler@lerctr.org>
2002-10-15 16:28 ` Josh Berkus <josh@agliodbs.com>
2002-10-15 18:54 ` Peter Eisentraut <peter_e@gmx.net>
2002-10-17 20:22 ` Larry Rosenman <ler@lerctr.org>
2002-10-17 22:50 ` Josh Berkus <josh@agliodbs.com>
2002-10-18 00:13 ` Larry Rosenman <ler@lerctr.org>
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