Thread: adding years to a date field
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
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
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:
select ii_purchased + your_interval_field * '1 year'::date as date from inventory_item;
Cheers,
Steve
I have to add a number of years to a date field. The years come from another field in the table.Assuming that the field is an int, multiply the value in your years column by a 1-year interval:
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
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?
On 06/04/2011 9:05 AM, Adrian Klaver wrote:
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 06/04/2011 9:05 AM, Adrian Klaver wrote:
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
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
On 06/04/2011 9:23 AM, Osvaldo Kussama wrote:
Christine Penner Ingenious Software 250-352-9495 chris@fp2.ca
On 06/04/2011 9:23 AM, Osvaldo Kussama wrote:
SELECT ii_purchased + ii_expected_life * '1 year'::interval FROM inventory_item;
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.