adding years to a date field

On 04/06/2011 08:54 AM, Christine Penner wrote:

I have to add a number of years to a date field. The years come from
another field in the table.

I know I can do this:
select (ii_purchased + interval '3 year') as date from inventory_item;

But I need to replace the 3 in 3 years with another field from the same
table ii_expected_life but I can't seem to do that without getting
syntax errors

Maybe something along lines:
select (ii_purchased + interval year_int::text||' year') as date from
inventory_item

I am guessing at the data type of the field.

--
Christine Penner
Ingenious Software
250-352-9495
chris@fp2.ca

--
Adrian Klaver
adrian.klaver@gmail.com

On 04/06/2011 08:54 AM, Christine Penner wrote:

I have to add a number of years to a date field. The years come from
another field in the table.

I know I can do this:
select (ii_purchased + interval '3 year') as date from inventory_item;

But I need to replace the 3 in 3 years with another field from the
same table ii_expected_life but I can't seem to do that without
getting syntax errors
--
Christine Penner
Ingenious Software
250-352-9495
chris@fp2.ca

Assuming that the field is an int, multiply the value in your years
column by a 1-year interval:

select ii_purchased + your_interval_field * '1 year'::date as date from
inventory_item;

Cheers,
Steve

This still gave me a sytax error. The other suggestion to multiply the
interval field by 1 year also gave me a syntax error.

ii_purchased is a timestamp without time zone
ii_expected_life is a smallint

Any other suggestions?

Christine Penner
Ingenious Software
250-352-9495
chris@fp2.ca

On Apr 6, 2011, at 12:18, Christine Penner wrote:

This still gave me a sytax error. The other suggestion to multiply the interval field by 1 year also gave me a syntax error.

What was the error? And it's preferable to do multiply rather than do the equivalent of an eval on some string.

select (current_timestamp at time zone 'utc' + CAST(3 AS SMALLINT) * interval '1 year');
?column?
----------------------------
2014-04-06 16:27:30.273562
(1 row)

Michael Glaesemann
grzm seespotcode net

This worked. Thanks

Christine Penner
Ingenious Software
250-352-9495
chris@fp2.ca

On Wed, Apr 06, 2011 at 09:18:15AM -0700, Christine Penner wrote:

This still gave me a sytax error. The other suggestion to multiply
the interval field by 1 year also gave me a syntax error.

...

Any other suggestions?

...

Christine Penner
Ingenious Software

Live up to to it ?

Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

On 04/06/11 9:10 AM, Steve Crawford wrote:

select ii_purchased + your_interval_field * '1 year'::date as date
from inventory_item;

that SHOULD be written as...

select ii_purchased + your_interval_field * interval '1 year' as date
from inventory_item;

On 04/06/2011 12:28 PM, John R Pierce wrote:

On 04/06/11 9:10 AM, Steve Crawford wrote:

select ii_purchased + your_interval_field * '1 year'::date as date
from inventory_item;

that SHOULD be written as...

select ii_purchased + your_interval_field * interval '1 year' as date
from inventory_item;

Correct. I meant ...::interval... but hadn't had my first coffee. :)

Cheers,
Steve

On 04/06/11 1:18 PM, Steve Crawford wrote:

select ii_purchased + your_interval_field * interval '1 year' as date
from inventory_item;

Correct. I meant ...::interval...

FWIW, '1 year'::interval implies a cast, that is a postgresql-specific
notation for cast('1 year' as interval), while interval '1 year' is the
SQL standard notation for an interval constant. yes, the end results
are the same, the parser/optimizer will look at that cast and do it in
advance rather than at execution time since the input is invariant.