Thread: 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