Thread: Should casting to integer produce same result as trunc()
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.
Expect it is my understanding though.
Can someone set me straight?
And thank you all for a wonderfull RDBMS.
Allan
select version();
version
------------------------------------------------------------------------
-------------
PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.0
(SUSE Linux)
(1 row)
-- trying to squash timestamps to five minutes. The result should be
different. Use casting.
select timestamp without time zone 'epoch' + (((extract( epoch from
'2011-08-22 08:37:30'::timestamp ) + 10 * 3600) / 300 )::integer) * 300
* INTERVAL '1
second', timestamp without time zone 'epoch' + (((extract( epoch from
'2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300 )::integer) * 300
* INTERVAL
'1 second';
?column? | ?column?
---------------------+---------------------
2011-08-22 08:40:00 | 2011-08-22 08:40:00
(1 row)
-- The result is different, correct. Use trunc().
select timestamp without time zone 'epoch' + trunc(((extract( epoch from
'2011-08-22 08:37:30'::timestamp ) + 10 * 3600) / 300 )) * 300 *
INTERVAL '1 sec
ond', timestamp without time zone 'epoch' + trunc(((extract( epoch from
'2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300 )) * 300 *
INTERVAL '1 seco
nd';
?column? | ?column?
---------------------+---------------------
2011-08-22 08:35:00 | 2011-08-22 08:40:00
(1 row)
-- Raw seconds. Different as expected.
select (((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 *
3600) )::integer), (((extract( epoch from '2011-08-22
08:42:30'::timestamp ) + 10
* 3600) )::integer);
int4 | int4
------------+------------
1314002250 | 1314002550
(1 row)
-- 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)
select (1314002250 / 300)::integer, (1314002550 / 300)::integer;
int4 | int4
---------+---------
4380007 | 4380008
(1 row)
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,
pleaseadvise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses
containedin this email or any attachments.
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, pleaseadvise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses containedin this email or any attachments.