The following bug has been logged online:
Bug reference: 4231
Logged by: Geoff Tolley
Email address: geoff@polimetrix.com
PostgreSQL version: 8.3.1
Operating system: CentOS 4.6 x86_64 (custom-compiled Linux 2.6.24.2 kernel
unpatched from kernel.org)
Description: Interval silent seconds 32-bit wraparound when given as
text
Details:
When you make an interval by giving it a text representation of a number of
seconds, it appears to have a silent 32-bit wraparound (the example here is
from some raw SPSS data from a colleague; apparently it uses October 14th
1584 as its base so the seconds output tends to be >> 2^31):
select '1584-10-14 00:00'::timestamp + '13424373496 seconds'::interval;
?column?
---------------------
1601-11-17 21:13:28
(1 row)
select 13424373496::bigint % (2 ^ 32)::bigint;
?column?
-----------
539471608
(1 row)
select '1584-10-14 00:00'::timestamp + '539471608 seconds'::interval;
?column?
---------------------
1601-11-17 21:13:28
(1 row)
However, the error is actually raised if it's not a string:
select '1584-10-14 00:00'::timestamp + 13424373496::interval;
ERROR: cannot cast type bigint to interval
LINE 1: ...elect '1584-10-14 00:00'::timestamp + 13424373496::interval;
^
HTH,
Geoff
