Postgres Pro
Downloads
Home   >   mailing lists

Thread: casting for dates

casting for dates

From
Vince Vielhaber
Date:
I'm trying to use an integer from a table to add/subtract time in months.
IOW:

create table foo(nummonths int);

select now() - nummonths months;

So far nothing I've tried will work - short of a function.  Is there a
way to do this?

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net        56K Nationwide Dialup from $16.00/mo
atPop4 Networking       Online Campground Directory    http://www.camping-usa.com      Online Giftshop Superstore
http://www.cloudninegifts.com
==========================================================================

Re: casting for dates

From
Andrew McMillan
Date:
On Thu, 2001-09-27 at 08:30, Vince Vielhaber wrote:
> 
> I'm trying to use an integer from a table to add/subtract time in months.
> IOW:
> 
> create table foo(nummonths int);
> 
> select now() - nummonths months;

newsroom=# select now() - interval( text(3) || ' months');       ?column?        
------------------------2001-06-27 08:56:27+12
(1 row)


Crude, but hey: it works :-)

Cheers,                SAndrew.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7217    MOB: +64(21)635-694    OFFICE: +64(4)499-2267

Re: casting for dates

From
"Mitch Vincent"
Date:
Will

SELECT now() - 'nummonths months'::interval ;

work?


----- Original Message -----
From: "Vince Vielhaber" <vev@michvhf.com>
To: <pgsql-hackers@postgresql.org>
Sent: Wednesday, September 26, 2001 4:30 PM
Subject: [HACKERS] casting for dates


>
> I'm trying to use an integer from a table to add/subtract time in months.
> IOW:
>
> create table foo(nummonths int);
>
> select now() - nummonths months;
>
> So far nothing I've tried will work - short of a function.  Is there a
> way to do this?
>
> Vince.
> --
> ==========================================================================
> Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
>          56K Nationwide Dialup from $16.00/mo at Pop4 Networking
>         Online Campground Directory    http://www.camping-usa.com
>        Online Giftshop Superstore    http://www.cloudninegifts.com
> ==========================================================================
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: casting for dates

From
Ryan Mahoney
Date:
Haven't tried yet, but perhaps casting nummonths to an interval datatype
would do the trick.

-r

At 04:30 PM 9/26/01 -0400, Vince Vielhaber wrote:


>I'm trying to use an integer from a table to add/subtract time in months.
>IOW:
>
>create table foo(nummonths int);
>
>select now() - nummonths months;
>
>So far nothing I've tried will work - short of a function.  Is there a
>way to do this?
>
>Vince.
>--
>==========================================================================
>Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net
>          56K Nationwide Dialup from $16.00/mo at Pop4 Networking
>         Online Campground Directory    http://www.camping-usa.com
>        Online Giftshop Superstore    http://www.cloudninegifts.com
>==========================================================================
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
>---
>Incoming mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01

Re: casting for dates

From
Vince Vielhaber
Date:
On 27 Sep 2001, Andrew McMillan wrote:

> On Thu, 2001-09-27 at 08:30, Vince Vielhaber wrote:
> >
> > I'm trying to use an integer from a table to add/subtract time in months.
> > IOW:
> >
> > create table foo(nummonths int);
> >
> > select now() - nummonths months;
>
> newsroom=# select now() - interval( text(3) || ' months');
>         ?column?
> ------------------------
>  2001-06-27 08:56:27+12
> (1 row)
>
>
> Crude, but hey: it works :-)

It certainly does!  Thanks!

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net        56K Nationwide Dialup from $16.00/mo
atPop4 Networking       Online Campground Directory    http://www.camping-usa.com      Online Giftshop Superstore
http://www.cloudninegifts.com
==========================================================================

Re: casting for dates

From
Vince Vielhaber
Date:
On Wed, 26 Sep 2001, Mitch Vincent wrote:

> Will
>
> SELECT now() - 'nummonths months'::interval ;
>
> work?

Unfortunately no.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH    email: vev@michvhf.com    http://www.pop4.net        56K Nationwide Dialup from $16.00/mo
atPop4 Networking       Online Campground Directory    http://www.camping-usa.com      Online Giftshop Superstore
http://www.cloudninegifts.com
==========================================================================