subtracting from a date

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

thanks

The basics are easy. Cast the string to a date and subtract the
appropriate interval:
'2013-04-01'::date - '1 year'::interval

BUT...
PostgreSQL, like all programs, makes certain assumptions about dates and
intervals. Generally they are good but you need to be sure they match
your requirements lest you be surprised. For example:

1 day may or may not be 24 hours (DST changeovers)

1 month has varying numbers of days

Same thing with 1 year (leap years)

Certain calculations will give different results depending on what
timezone you set due to the different scheduling and handling of
summer/winter time offsets.

Operator precedence is important. You might expect "select
'2012-02-29'::date - '1 year'::interval + '1 year'::interval;" to return
2012-02-29 but it will return 2012-02-28 since February 2011 has no 29th
and 2011-02-28 plus a year is 2012-02-28.

For an amusing take on date/time calculations see:
http://www.youtube.com/watch?v=-5wpm-gesOY

Cheers,
Steve

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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;

---  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:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

jvsrvcs wrote

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;

---  I get:

ERROR: query has no destination for result data

The relevant section of the documentation you need to study is:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-statements.html

There are two basic ways to assign to variables:

variable := expression
SELECT expression[s] INTO variable[s] FROM ... || SELECT expression[s] FROM
... INTO variable[s]

Your problem is that:

SELECT expression AS "variable" FROM ... simply provides an alias for the
expression and has nothing to do with any variables in the surrounding
program. The "INTO" keyword is needed to avoid ambiguity.

pl/pgsql has the unique behavior that using SELECT without INTO results in
an error. If you really need to execute a SELECT and ignore the content
selected you have to use PERFORM. The error you saw was this behavior in
action.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/subtracting-from-a-date-tp5790891p5790923.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general