Should casting to integer produce same result as trunc()

On 11 Oct 2011, at 2:55, Harvey, Allan AC wrote:

Hi all,

Had to squash timestamps to the nearest 5 minutes and things went wrong.

My simple understanding of trunc() and casting to an integer says that
there is a bug here.

I think you may be right there, something about the rounding in the cast seems wrong.

-- should be different but are not.
select (((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 *
3600) / 300 )::integer), (((extract( epoch from '2011-08-22
08:42:30'::timestamp
) + 10 * 3600) / 300 )::integer);
int4 | int4
---------+---------
4380008 | 4380008
(1 row)

Without the cast, that gives (I'm in a different TZ apparently):

select (extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 *
3600) / 300, (extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300;
?column? | ?column?
-----------+-----------
4380103.5 | 4380104.5
(1 row)

Which the type-cast should round to 4380103 and 4380104 respectively.
It doesn't:

select ((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 *
3600) / 300)::integer, floor((extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300);
int4 | floor
---------+---------
4380104 | 4380104
(1 row)

Floor() works fine though:

select floor((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 *
3600) / 300), floor((extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300);
floor | floor
---------+---------
4380103 | 4380104
(1 row)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

Alban Hertroys <haramrae@gmail.com> writes:

On 11 Oct 2011, at 2:55, Harvey, Allan AC wrote:

My simple understanding of trunc() and casting to an integer says that
there is a bug here.

Which the type-cast should round to 4380103 and 4380104 respectively.
It doesn't:

That's because a cast from float to int rounds, it doesn't truncate.

regression=# select (4.7::float8)::int;
int4
------
5
(1 row)

regards, tom lane

On 11 October 2011 15:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alban Hertroys <haramrae@gmail.com> writes:

On 11 Oct 2011, at 2:55, Harvey, Allan AC wrote:

My simple understanding of trunc() and casting to an integer says that
there is a bug here.

Which the type-cast should round to 4380103 and 4380104 respectively.
It doesn't:

That's because a cast from float to int rounds, it doesn't truncate.

regression=# select (4.7::float8)::int;
 int4
------
   5
(1 row)

I figured it would be something like that. Is that how it's defined in
the SQL standard?

All other programming languages I've come to know truncate floats on
such casts (C, php, python, java, to name a few).
This is probably quite surprising to people used to these languages.

I agree that rounding is the right thing to do. We are probably just
too used to programming languages that truncate instead of round
because it was more convenient to implement it that way at the time.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Alban Hertroys <haramrae@gmail.com> writes:

On 11 October 2011 15:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:

That's because a cast from float to int rounds, it doesn't truncate.

I figured it would be something like that. Is that how it's defined in
the SQL standard?

SQL99 says

Whenever an exact or approximate numeric value is assigned to
an exact numeric value site, an approximation of its value that
preserves leading significant digits after rounding or truncating
is represented in the declared type of the target. The value is
converted to have the precision and scale of the target. The choice
of whether to truncate or round is implementation-defined.

An approximation obtained by truncation of a numeric value N for an
<exact numeric type> T is a value V in T such that N is not closer
to zero than is V and there is no value in T between V and N.

An approximation obtained by rounding of a numeric value N for an
<exact numeric type> T is a value V in T such that the absolute
value of the difference between N and the numeric value of V is
not greater than half the absolute value of the difference between
two successive numeric values in T. If there is more than one such
value V, then it is implementation-defined which one is taken.

or in short, "you can do it in any reasonable fashion". It looks like
our code for this has used rint() since the day it was put in,
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=64d9b508939fb15d72fdfa825ee8938506764d66

regards, tom lane

-----Original Message-----
From: Alban Hertroys [mailto:haramrae@gmail.com]
Sent: Wednesday, 12 October 2011 1:35 AM
To: Tom Lane
Cc: Harvey, Allan AC; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Should casting to integer produce same
result as trunc()

On 11 October 2011 15:41, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Alban Hertroys <haramrae@gmail.com> writes:

On 11 Oct 2011, at 2:55, Harvey, Allan AC wrote:

My simple understanding of trunc() and casting to an

integer says that

there is a bug here.

Which the type-cast should round to 4380103 and 4380104

respectively.

It doesn't:

That's because a cast from float to int rounds, it doesn't truncate.

regression=# select (4.7::float8)::int;
 int4
------
   5
(1 row)

I figured it would be something like that. Is that how it's defined in
the SQL standard?

All other programming languages I've come to know truncate floats on
such casts (C, php, python, java, to name a few).
This is probably quite surprising to people used to these languages.

Thanks guys, yes C/C++ is my poison, forming my expectations.

Allan

The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments.