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.