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