Skip site navigation (1) Skip section navigation (2)

Re: BUG #2403: Date arithemtic using INTERVAL in UPDATE command

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: "Harry E(dot) Clarke" <Harry(dot)Clarke(at)metrosky(dot)co(dot)uk>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2403: Date arithemtic using INTERVAL in UPDATE command
Date: 2006-04-24 18:15:53
Message-ID: 200604241815.k3OIFrb21461@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-bugs
Harry E. Clarke wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:      2403
> Logged by:          Harry E. Clarke
> Email address:      Harry(dot)Clarke(at)metrosky(dot)co(dot)uk
> PostgreSQL version: 8.1.3
> Operating system:   Suse Linux 10.0
> Description:        Date arithemtic using INTERVAL in UPDATE command does
> not work
> Details: 
> 
> Entering the command
> 
> UPDATE table SET col_date = col_date - INTERVAL '100' YEAR where condition;
> 
> does not perform any date aritimetic, and thus the date value in col_date
> remains unchanged. col_date contains a value such as '2039-12-07'. If the
> command
> 
> UPDATE table SET col_date = DATE '1939-12-07' where condition;
> 
> is entered, the command executes as expected.

That is definately a bug:

	test=> SELECT INTERVAL '100' YEAR;
	 interval
	----------
	 00:00:00
	(1 row)

As a work-around until we fix it, please use:
	
	test=> SELECT INTERVAL '100 year';
	 interval
	-----------
	 100 years
	(1 row)

with the 'YEAR' in the quotes.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

Responses

pgsql-bugs by date

Next:From: Bruno Wolff IIIDate: 2006-04-24 18:56:22
Subject: Re: BUG #2403: Date arithemtic using INTERVAL in UPDATE command does not work
Previous:From: RIMBAUDDate: 2006-04-24 07:25:31
Subject: BUG #2405: how to know if a cursor is open

pgsql-advocacy by date

Next:From: Bruno Wolff IIIDate: 2006-04-24 18:56:22
Subject: Re: BUG #2403: Date arithemtic using INTERVAL in UPDATE command does not work
Previous:From: Michael DeanDate: 2006-04-24 17:42:29
Subject: Re: Fun forward from mod_perl list: Migrating from

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group