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

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?

Thanks,
Otto

2009/5/23 Ludwig Kniprath <ludwig@kni-online.de>

Hi,

On 8.2 this comparision is also not true:

select '240:0:0.3'::interval = '10 0:0:0.3'::interval;

But without milliseconds it's true.

Is this also because interval is represented internally as a floating point
value?

On 8.3 this test does not fail.

Best regards,
Otto

2009/5/23 Havasvölgyi Ottó <havasvolgyi.otto@gmail.com>

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.

I mean the Win32 distribution on the PgSql site. I always used that.
It would be very good if these data types were exact by default, even if
that's a bit slower.

Otto

2009/5/23 Christophe <xof@thebuild.com>

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

Thanks, It's off in both 8.2 and 8.3.
What will be the default in 8.4?

Best regards,
Otto

2009/5/23 Alvaro Herrera <alvherre@commandprompt.com>

=?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

2009/5/24 Tom Lane <tgl@sss.pgh.pa.us>

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.