Re: incrementing and decrementing dates by day increments programmatically

On Mon, Oct 27, 2003 at 01:40:53AM -0800, Neil Zanella wrote:
> alvherre@dcc.uchile.cl (Alvaro Herrera) wrote in message
>
> > You can of course do
> > SELECT now() + CAST('5 day' AS interval);
>
> Perhaps I should get myself a copy of the relevant parts of the SQL 99
> standard.  How would you do the above in standard SQL?

I think one standard way of doing the above would be
SELECT CURRENT_TIMESTAMP + CAST('5 day' AS interval);

Or, as pointed out by Tom Lane and someone else, if you don't need the
time part,
SELECT CURRENT_DATE + 5;

> > For the date -I format you can use something like
> > SELECT to_char(now() + 5 * '1 day'::interval, 'YYYY-MM-DD');
>
> I believe Oracle also has a to_char() function. Is this to_char() function
> part of standard SQL or is it just a coincidence that both DBMSs support
> such a function call? I wonder whether the PostgreSQL to_char()
> function is compatible with the Oracle one.

AFAIK the main motivation to create the to_char() function in the first
place was in fact Oracle compatibility.  If you want to do such a thing
in a standard manner, you should probably do

SELECT    EXTRACT(year FROM a) || '-' ||
    EXTRACT(month FROM a) || '-' ||
    EXTRACT(day FROM a)
FROM    (SELECT CURRENT_DATE + 5 AS a) AS foo;

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"I would rather have GNU than GNOT."  (ccchips, lwn.net/Articles/37595/)