Re: possibly a bug?

beta=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3
(1 row)

Is this a bug or Am i Wrong?
Or can be done this with another sql statement
I want to know only the next day of a date

beta=# SELECT ('2001-10-26'::date + '1 day'::interval)::date;
?column?
------------
2001-10-27
(1 row)

beta=# SELECT ('2001-10-27'::date + '1 day'::interval)::date;
?column?
------------
2001-10-28
(1 row)

beta=# SELECT ('2001-10-28'::date + '1 day'::interval)::date;
?column?
------------
2001-10-28
(1 row)

Ewald Geschwinde

Ewald Geschwinde <webmaster(at)geschwinde(dot)net> writes:
> beta=# SELECT ('2001-10-28'::date + '1 day'::interval)::date;
> ?column?
> ------------
> 2001-10-28
> (1 row)

Try using date arithmetic, rather than timestamp arithmetic:

regression=# SELECT '2001-10-28'::date + 1;
?column?
------------
2001-10-29
(1 row)

The problem with the other is that type interval considers '1 day'
to mean '24 hours', which is not what you want. Since 10/28 is
a DST transition day (at least here in the USA), there's a difference.
What you're really getting is a timestamp addition:

regression=# SELECT ('2001-10-28'::date + '1 day'::interval);
?column?
------------------------
2001-10-28 23:00:00-05
(1 row)

which doesn't produce the desired result when you coerce the timestamp
back to date.

However, adding a plain integer to a date will do what you want.

regards, tom lane

Ewald,

> Is this a bug or Am i Wrong?

It's not a bug, it's a feature :-)

Your problem is that you're convertng back and forth between DATE
(which does not track hours) and TIMESTAMP + INTERVAL, which does
track hours. Therefore you arrive at this problem:

> beta=# SELECT ('2001-10-28'::date + '1 day'::interval)::date;
> ?column?
> ------------
> 2001-10-28
> (1 row)

Ah, but you're not seeing the whole story:

beta=# SELECT ('2001-10-28'::date + '1 day'::interval)::TIMESTAMP
?column?
------------
2001-10-28 23:00:00
(1 row)

Aha! And, if we look at a calendar, we see that 10.28.2001 was the end
of daylight savings time in 2001!

For your purposes, it would be better not to muddy the waters by
tinkering with the vagaries of TIMESTAMP. Thus, you should:

SELECT ('2001-10-28'::DATE + 1);
?column?
------------
2001-10-29
(1 row)

FOr more info, see my DATE/TIME FAQ on http://techdocs.postgtresql.org/

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco