Re: subtracting from a date - Mailing list pgsql-general

From Jerry Sievers
Subject Re: subtracting from a date
Date
Msg-id 86d2j0m72z.fsf@jerry.enova.com
Whole thread Raw
In response to Re: subtracting from a date  (Jay Vee <jvsrvcs@gmail.com>)
List pgsql-general
Jay Vee <jvsrvcs@gmail.com> writes:

> I tried that but get an error
> ? ? v_start_date date;
> ? ? v_minus_one_year date;
>
> I have v_start_date to start with and want to subtract one year and put into v_minus_one_year
> ??
> ? select v_start_date - interval '1 yr' as v_minus_one_year;


sj$ cat q
create or replace function foo(date)
returns date
as $$
declare
    foo date;
begin
    select into foo $1 - '1 year'::interval;
    return foo;
end
$$
language plpgsql;

select foo(current_date);
sj$ psql -f q
SET
CREATE FUNCTION
    foo
------------
 2013-02-06
(1 row)

HTH
>
> --- ?I get:
>
> ERROR: ?query has no destination for result data
>
> On Thu, Feb 6, 2014 at 10:31 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
>
>     On 02/06/2014 09:25 AM, Jay Vee wrote:
>
>         I have reviewed working with dates documentation but some things are not
>         clear and I cannot get an example to work for what I need.
>
>         I am passing a date into a stored function like '2013-04-01'
>
>         The stored function accepts this string as a date type.
>
>         Within the function, I need to:
>         1. ?subtract one year from this date into another date type
>         2. ?subtract one month from this date into another date type
>         3. ?subtract one day from this date into another date type
>
>         Are there any examples of this? ?This is not a timestamp type, but a
>         date type.
>
>     Something like this:
>
>     test=> select '2013-04-01'::date - interval '1 yr';
>     ? ? ? ?column?
>     ---------------------
>     ?2012-04-01 00:00:00
>     (1 row)
>
>     test=> select '2013-04-01'::date - interval '1 month';
>     ? ? ? ?column?
>     ---------------------
>     ?2013-03-01 00:00:00
>     (1 row)
>
>     test=> select '2013-04-01'::date - interval '1 day';
>     ? ? ? ?column?
>     ---------------------
>     ?2013-03-31 00:00:00
>
>     You did say what language you are using for the function so the assignment will depend on that.
>
>         thanks
>
>     --
>     Adrian Klaver
>     adrian.klaver@gmail.com
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


pgsql-general by date:

Previous
From: Jay Vee
Date:
Subject: Re: subtracting from a date
Next
From: Scott Marlowe
Date:
Subject: Re: Offending My Tender Sensibilities -OR- OLTP on a Star Schema