Search Postgresql Archives

Re: Should casting to integer produce same result as trunc()

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux