Thread: 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@geschwinde.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@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco