Обсуждение: 8.3: timestamp subtraction

    Havasvölgyi Ottó wrote:

> I don't know why this query returns false:
>
> SELECT '20040506 070809.010000'::timestamp(6) - '20010203
> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
> millisecond'::interval;
> If I just subtract the two timestamps, its result is the interval I
> specified.
>
> What may cause this?

It works for me:

test=> SELECT '20040506 070809.010000'::timestamp(6) -
  '20010203 040506.007000'::timestamp(6)=
  '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
 ?column?
----------
 t
(1 row)

test=> select version();
                          version

-----------------------------------------------------------------------
-----------------------------
 PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Ubuntu 4.3.2-1ubuntu11) 4.3.2
(1 row)

Best regards,
--
 Daniel
 PostgreSQL-powered mail user agent and storage:
 http://www.manitou-mail.org

On May 23, 2009, at 9:13 AM, Daniel Verite wrote:
>> I don't know why this query returns false:
>> SELECT '20040506 070809.010000'::timestamp(6) - '20010203
>> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
>> millisecond'::interval;
>> If I just subtract the two timestamps, its result is the interval I
>> specified.
>> What may cause this?
>
> It works for me:
>
> test=> SELECT '20040506 070809.010000'::timestamp(6) -
> '20010203 040506.007000'::timestamp(6)=
> '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
> ?column? ----------
> t
> (1 row)

Could this be due to the OP's build of PG using floating point
timestamps?

On Sat, May 23, 2009 at 7:18 AM, Christophe <xof@thebuild.com> wrote:
>
> On May 23, 2009, at 9:13 AM, Daniel Verite wrote:
>>>
>>> I don't know why this query returns false:
>>> SELECT '20040506 070809.010000'::timestamp(6) - '20010203
>>> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
>>> millisecond'::interval;
>>> If I just subtract the two timestamps, its result is the interval I
>>> specified.
>>> What may cause this?
>>
>> It works for me:
>>
>> test=> SELECT '20040506 070809.010000'::timestamp(6) -
>> '20010203 040506.007000'::timestamp(6)=
>> '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
>> ?column? ----------
>> t
>> (1 row)
>
> Could this be due to the OP's build of PG using floating point timestamps?

That's what I'm thinking.

Scott Marlowe schrieb:
> On Sat, May 23, 2009 at 7:18 AM, Christophe <xof@thebuild.com> wrote:
>
>> On May 23, 2009, at 9:13 AM, Daniel Verite wrote:
>>
>>>> I don't know why this query returns false:
>>>> SELECT '20040506 070809.010000'::timestamp(6) - '20010203
>>>> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
>>>> millisecond'::interval;
>>>> If I just subtract the two timestamps, its result is the interval I
>>>> specified.
>>>> What may cause this?
>>>>
>>> It works for me:
>>>
>>> test=> SELECT '20040506 070809.010000'::timestamp(6) -
>>> '20010203 040506.007000'::timestamp(6)=
>>> '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
>>> ?column? ----------
>>> t
>>> (1 row)
>>>
>> Could this be due to the OP's build of PG using floating point timestamps?
>>
>
> That's what I'm thinking
Me too, a testquery-result on a Windows-System with version "PostgreSQL
8.3.0, compiled by Visual C++ build 1400":

SELECT ('20040506 070809.010000'::timestamp(6) - '20010203
040506.007000'::timestamp(6) - '1188 day 3 hour 3 minute 3 second 3
millisecond'::interval) * 1e10;

=> -00:01:28.220986

On May 23, 2009, at 10:44 AM, Havasvölgyi Ottó wrote:

> Thanks.
> I tested the standard Win32 distribution of 8.3.6.
> The same happens on 8.2. But on 8.0 it works.
>
> When I don't use milliseconds, then it works.
>
> Will 8.4 work fine on Win32 again?

If the issue is using floating point timestamps, then the particular
version of PostgreSQL isn't the issue; it's whether the distribution
you were using was built with integer or floating point timestamps.

Havasvölgyi Ottó escribió:
> I mean the Win32 distribution on the PgSql site. I always used that.

If you want to find out whether a particular build used floating point or
integer datetimes, issue "SHOW integer_datetimes".

If it says "off", then it's floating point.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

=?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <havasvolgyi.otto@gmail.com> writes:
> Thanks, It's off in both 8.2 and 8.3.

As was already stated, that depends on which build you're using.
(And no, "the Win32 distribution on the PgSql site" is not a unique
description, not even for a single PG version.)

> What will be the default in 8.4?

The same comment will apply to 8.4.  There is a general trend away from
floating point timestamps, but there will probably be some builds
continuing to use them for a long time to come, because of compatibility
considerations.

            regards, tom lane

On 2009-05-23, Havasvölgyi Ottó <havasvolgyi.otto@gmail.com> wrote:
> --0016364c779590a8c0046a9321b6
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: 7bit
>
> Hi,
>
> I don't know why this query returns false:
>
> SELECT '20040506 070809.010000'::timestamp(6) - '20010203
> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
> millisecond'::interval;
> If I just subtract the two timestamps, its result is the interval I
> specified.
>
> What may cause this?

floating point arithmetic.

especially floats involving fractions.