Hi all,<br /><br />When running some QE tests at VMware, we found an error with extract handling timezones.<br />Please
seebelow:<br />postgres=# show timezone;<br /> TimeZone <br />------------<br /> Asia/Tokyo<br />(1 row)<br
/>postgres=#select now();<br /> now <br />-------------------------------<br /> 2013-03-12
14:54:28.911298+09<br/>(1 row)<br />postgres=# select extract(day from ((CAST(-3 || 'day' as interval)+now()) -
now()));<br/> date_part <br /> -----------<br /> -3<br />(1 row)<br />postgres=# set timezone =
'US/Pacific';<br/>SET<br />postgres=# select now();<br /> now <br
/>-------------------------------<br/> 2013-03-11 22:56:10.317431-07<br /> (1 row)<br />postgres=# select extract(day
from((CAST(-3 || 'day' as interval)+now()) - now()));<br /> date_part <br />-----------<br /> -2<br />(1 row)<br
/>HereI believe that the correct result should be -3.<br /><br /> Note that it passes with values upper than -2 and
lowerthan -127:<br />postgres=# select extract(day from ((CAST(-128 || 'day' as interval)+now()) - now()));<br
/> date_part<br />-----------<br /> -128<br />(1 row)<br /> postgres=# select extract(day from ((CAST(-127 ||
'day'as interval)+now()) - now()));<br /> date_part <br />-----------<br /> -126<br />(1 row)<br />postgres=#
selectextract(day from ((CAST(-2 || 'day' as interval)+now()) - now()));<br /> date_part <br />-----------<br
/> -1<br />(1 row)<br />postgres=# select extract(day from ((CAST(-1 || 'day' as interval)+now()) - now()));<br
/> date_part<br />-----------<br /> -1<br />(1 row)<br /><br />Also note that this happens only with the
timezoneset where time -1day.<br /> postgres=# set timezone to 'Asia/Tokyo';<br />SET<br />postgres=# select
extract(dayfrom ((CAST(-127 || 'day' as interval)+now()) - now()));<br /> date_part <br />-----------<br />
-127<br/>(1 row)<br />postgres=# select extract(day from ((CAST(-100 || 'day' as interval)+now()) - now()));<br />
date_part<br />-----------<br /> -100<br />(1 row)<br />postgres=# select extract(day from ((CAST(-2 || 'day' as
interval)+now())- now()));<br /> date_part <br />-----------<br /> -2<br />(1 row)<br /><br />I also tested with
PGon master until 8.4 and could reproduce the problem.<br /><br />Regards,<br />-- <br />Michael<br />
