Обсуждение: Problem with intervals

I'm getting an unexpected result using intervals in an expression:

select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;    date
------------ 2003-10-26
(1 row)

When I get rid of the date cast it becomes clear what is happening:

select '2003-10-26 0:00:00'::timestamp + '1 day'::interval;        ?column?
------------------------ 2003-10-26 23:00:00-08
(1 row)

I assumed '1 day' would always increment the date by 1, but it appears 
that '1 day' just means '24 hours', and due to the daylight/standard 
time shift, October 26 was 25 hours long this year.

Is this a Postgres bug, or is this correct SQL behavior?  I'm running 
Postgres 7.2.2.

Bob S.

On Tuesday 02 December 2003 05:09, Bob Smith wrote:
> I'm getting an unexpected result using intervals in an expression:
>
> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
>      date
> ------------
>   2003-10-26

> I assumed '1 day' would always increment the date by 1, but it appears
> that '1 day' just means '24 hours', and due to the daylight/standard
> time shift, October 26 was 25 hours long this year.
>
> Is this a Postgres bug, or is this correct SQL behavior?  I'm running
> Postgres 7.2.2.

Expected, because you're acting on a timestamp. When you start looking at time 
handling across timezones and daylight saving systems across the world it 
does get a bit complicated.

--  Richard Huxton Archonet Ltd

On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
> I'm getting an unexpected result using intervals in an expression:
> 
> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
>     date
> ------------
>  2003-10-26
> (1 row)

Try using '2003-10-26 0:00:00'::date + 1;
integers do not lie ;-)


> When I get rid of the date cast it becomes clear what is happening:
> 
> select '2003-10-26 0:00:00'::timestamp + '1 day'::interval;
>         ?column?
> ------------------------
>  2003-10-26 23:00:00-08
> (1 row)
> 
> Is this a Postgres bug, or is this correct SQL behavior?  I'm running 
> Postgres 7.2.2.

It has been discussed several times, Tom Lane offered to add 'day' as
a separate interval unit (like 'second' and 'month' at this moment),
but noone took a shot at it, AFAIK.


Note also, that in 7.3 "timestamp" means "timestamp without time zone",
while in 7.2 it's "timestamp with time zone".


-- 
Fduch M. Pravking

Bob Smith <bsmith@h-e.com> writes:
> '1 day' just means '24 hours'

Yup.

> Is this a Postgres bug, or is this correct SQL behavior?

It's arguably a bug, but it's unfixable without a significant change in
the internal representation and handling of intervals.  I don't know
when anyone will get annoyed enough to tackle it.  In the meantime,
consider using date +/- integer arithmetic instead of timestamp + interval.

> I'm running Postgres 7.2.2.

You really should be running something newer ...
        regards, tom lane

On Tuesday, Dec 2, 2003, at 03:53 US/Pacific, Alexander M. Pravking 
wrote:

> On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
>> I'm getting an unexpected result using intervals in an expression:
>>
>> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
>>     date
>> ------------
>>  2003-10-26
>> (1 row)
>
> Try using '2003-10-26 0:00:00'::date + 1;
> integers do not lie ;-)

Aha!  That solves my problem for now.  I had also discovered that using 
'25 hours'::interval works, but the integer approach is better.

> It has been discussed several times, Tom Lane offered to add 'day' as
> a separate interval unit (like 'second' and 'month' at this moment),
> but noone took a shot at it, AFAIK.
>
>
> Note also, that in 7.3 "timestamp" means "timestamp without time zone",
> while in 7.2 it's "timestamp with time zone".

Yet another reason to upgrade, I guess I'm gonna have to do it soon...

Thanks for your help!

Bob S.