Thread: incrementing and decrementing dates by day increments programmatically

Hello,

I know that PostgreSQL, like most database management systems, has a
function
call called NOW() that returns the current date. Is there a way to
return a datein PostgreSQL such that the output is in ISO 8601 format
(Unix 'date -I' format)but such that the date is not "today"'s date
but the date two days ago or five
days ahead of now? I have tried something like NOW() + 5 but that did
not work
(because the data types are incompatible, and SELECT NOW() +
'0000-00-01' does
not work either. I get the error:

ERROR:  Bad interval external representation '0000-00-01'

Thanks,

Neil

On Sat, Oct 25, 2003 at 09:35:35PM -0700, Neil Zanella wrote:
> Hello,
>
> I know that PostgreSQL, like most database management systems, has a
> function
> call called NOW() that returns the current date. Is there a way to
> return a datein PostgreSQL such that the output is in ISO 8601 format
> (Unix 'date -I' format)but such that the date is not "today"'s date
> but the date two days ago or five
> days ahead of now?

Certainly.  Try the following:
SELECT now() + 5 * '1 day'::interval;

Or, more verbose,
SELECT now() + 5 * CAST('1 day' AS interval);

You can of course do
SELECT now() + CAST('5 day' AS interval);

But the two previous examples can be more easily constructed in an SQL o
PL/pgSQL function.


For the date -I format you can use something like
SELECT to_char(now() + 5 * '1 day'::interval, 'YYYY-MM-DD');

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La tristeza es un muro entre dos jardines" (Khalil Gibran)

On Sat, 25 Oct 2003, Neil Zanella wrote:

> Hello,
>
> I know that PostgreSQL, like most database management systems, has a
> function
> call called NOW() that returns the current date. Is there a way to
> return a datein PostgreSQL such that the output is in ISO 8601 format
> (Unix 'date -I' format)but such that the date is not "today"'s date
> but the date two days ago or five
> days ahead of now? I have tried something like NOW() + 5 but that did
> not work
> (because the data types are incompatible, and SELECT NOW() +
> '0000-00-01' does
> not work either. I get the error:


If you want a date, I'd suggest something like
 CURRENT_DATE+5

The reason this works while, now()+5 doesn't is that now() doesn't return
a date, but a timestamp type (including time).

If you want time information, then probably
 CURRENT_TIMESTAMP + INTERVAL '5 days'

Postgres has a type called "interval" to deal with these kinds of
situations.  As you might expect, an interval is a duration of time, as
opposed to a fixed time ordinate like date or timestamp.  You can add
and subtract interval values from dates and timestamps.  Intervals need
to be specified as strings and then cast to interval.  You can do this
in two ways:

interval '5 days'
'5 days'::interval

So to get "five days ago", you would use

now() - interval '5 days'

Similarly, to get 40 minutes into the future

now() + interval '40 minutes'

The resultant date or timestamp value can then be expressed in whatever
format you please by using to_char()

BJ

Neil Zanella wrote:

>Hello,
>
>I know that PostgreSQL, like most database management systems, has a
>function
>call called NOW() that returns the current date. Is there a way to
>return a datein PostgreSQL such that the output is in ISO 8601 format
>(Unix 'date -I' format)but such that the date is not "today"'s date
>but the date two days ago or five
>days ahead of now? I have tried something like NOW() + 5 but that did
>not work
>(because the data types are incompatible, and SELECT NOW() +
>'0000-00-01' does
>not work either. I get the error:
>
>ERROR:  Bad interval external representation '0000-00-01'
>
>Thanks,
>
>Neil
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>

BlakJak <blakjak@blakjak.sytes.net> writes:
> So to get "five days ago", you would use
> now() - interval '5 days'

Actually, given that the OP seems to only want a date result and not a
time-of-day, I'd suggest something like

    current_date - 5

The date-plus-integer and date-minus-integer operators do exactly what
I think is being asked for.  timestamp-minus-interval does computations
including fractional days, which will just confuse matters
... especially near DST transition days.  For instance, right now I get

regression=# select now();
              now
-------------------------------
 2003-10-27 01:45:14.458268-05
(1 row)

regression=# select now() - interval '5 days';
           ?column?
------------------------------
 2003-10-22 02:45:20.22788-04
(1 row)

which is correct in one sense but is surely going to confuse some
people.

            regards, tom lane

alvherre@dcc.uchile.cl (Alvaro Herrera) wrote in message

> Certainly.  Try the following:
> SELECT now() + 5 * '1 day'::interval;
>
> Or, more verbose,
> SELECT now() + 5 * CAST('1 day' AS interval);
>
> You can of course do
> SELECT now() + CAST('5 day' AS interval);
>
> But the two previous examples can be more easily constructed in an SQL or
> PL/pgSQL function.

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?

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

Thanks,

Neil

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/)