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, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
