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
