incrementing and decrementing dates by day increments programmatically

[Neil Zanella]

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

[Alvaro Herrera]

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)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Stephan Szabo]

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'

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[BlakJak]

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

BlakJak <bl*****@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Neil Zanella]

al******@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

[Alvaro Herrera]

On Mon, Oct 27, 2003 at 01:40:53AM -0800, Neil Zanella wrote:

al******@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/)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org