Re: 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.