Thread: 8.3: timestamp subtraction

8.3: timestamp subtraction

From
Havasvölgyi Ottó
Date:
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?
 
Thanks,
Otto

Re: 8.3: timestamp subtraction

From
"Daniel Verite"
Date:
    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

Re: 8.3: timestamp subtraction

From
Christophe
Date:
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?

Re: 8.3: timestamp subtraction

From
Scott Marlowe
Date:
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.

Re: 8.3: timestamp subtraction

From
Ludwig Kniprath
Date:
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

Re: 8.3: timestamp subtraction

From
Havasvölgyi Ottó
Date:
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>
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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: 8.3: timestamp subtraction

From
Havasvölgyi Ottó
Date:
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>
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>

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: 8.3: timestamp subtraction

From
Christophe
Date:
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.

Re: 8.3: timestamp subtraction

From
Havasvölgyi Ottó
Date:
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>

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: 8.3: timestamp subtraction

From
Alvaro Herrera
Date:
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

Re: 8.3: timestamp subtraction

From
Havasvölgyi Ottó
Date:
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>
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

Re: 8.3: timestamp subtraction

From
Tom Lane
Date:
=?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

Re: 8.3: timestamp subtraction

From
Havasvölgyi Ottó
Date:
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>
Havasvölgyi Ottó <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

Re: 8.3: timestamp subtraction

From
Jasen Betts
Date:
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.