Thread: 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

Thanks Tom for your comments.

I meant the build in this directory: http://www.postgresql.org/ftp/binary/v8.3.6/win32/, and the builds for win32 of other versions in the binary directory.
What is the trend of these builds regarding floating point timestamps? For example what about 8.4?

Thanks,
Otto

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.