Postgres Pro
Downloads
Home   >   mailing lists

Thread: Date Interval

Date Interval

From
"Magdalena Komorowska"
Date:
Hi,
I hale a problem with counting interwal and I can't find what to do with
this. 
I have two fields in the table:   Column       |  Type   | Modifiers
-----------------+---------+-----------date_in         | date    |interwal_months | numeric |
-----------------+---------+-----------

Query
SELECT date_in + INTERVAL '3 MONTH' FROM any_table
works fine of course. 

However, how to do something like that?
SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table

I hope I'm just blind.. ;-)
Thanks for any help.
MK

Re: Date Interval

From
Bruce Momjian
Date:
Magdalena Komorowska wrote:
> Hi,
> I hale a problem with counting interwal and I can't find what to do with
> this. 
> I have two fields in the table:
>     Column       |  Type   | Modifiers
> -----------------+---------+-----------
>  date_in         | date    |
>  interwal_months | numeric |
> -----------------+---------+-----------
> 
> Query
> SELECT date_in + INTERVAL '3 MONTH' FROM any_table
> works fine of course. 
> 
> However, how to do something like that?
> SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table

How about this?
test=> CREATE TABLE test(x TEXT);CREATE TABLEtest=> INSERT INTO test VALUES ('3');INSERT 0 1test=> SELECT
current_timestamp+ cast(x || ' months' AS INTERVAL) FROMtest;           ?column?-------------------------------
2006-03-0611:53:05.574279-05(1 row)
 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073

Re: Date Interval

From
Michael Fuhr
Date:
On Tue, Dec 06, 2005 at 11:54:05AM -0500, Bruce Momjian wrote:
> test=> SELECT current_timestamp + cast(x || ' months' AS INTERVAL) FROM
> test;
>            ?column?
> -------------------------------
>  2006-03-06 11:53:05.574279-05
> (1 row)

Or another way:

test=> CREATE TABLE test (x numeric);
CREATE TABLE
test=> INSERT INTO test VALUES (3);
INSERT 0 1
test=> SELECT current_timestamp + x * interval'1 month' FROM test;          ?column?            
-------------------------------2006-03-06 12:07:48.112765-05
(1 row)

-- 
Michael Fuhr

Re: Date Interval

From
Harald Fuchs
Date:
In article <200512061654.jB6Gs5d14178@candle.pha.pa.us>,
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> Magdalena Komorowska wrote:
>> Hi,
>> I hale a problem with counting interwal and I can't find what to do with
>> this. 
>> I have two fields in the table:
>> Column       |  Type   | Modifiers
>> -----------------+---------+-----------
>> date_in         | date    |
>> interwal_months | numeric |
>> -----------------+---------+-----------
>> 
>> Query
>> SELECT date_in + INTERVAL '3 MONTH' FROM any_table
>> works fine of course. 
>> 
>> However, how to do something like that?
>> SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table

> How about this?

>     test=> CREATE TABLE test(x TEXT);
>     CREATE TABLE
>     test=> INSERT INTO test VALUES ('3');
>     INSERT 0 1
>     test=> SELECT current_timestamp + cast(x || ' months' AS INTERVAL) FROM
>     test;
>                ?column?
>     -------------------------------
>      2006-03-06 11:53:05.574279-05
>     (1 row)

Since Magdalena doesn't store the number of months in a string, the
following might be more convenient:
 SELECT date_in + interwal_months * INTERVAL '1 MONTH' FROM any_table

Re: Date Interval

From
"Magdalena Komorowska"
Date:
It works great, very nice method :-)
thanks a lot! 

MK