The following bug has been logged online:
Bug reference: 2661
Logged by: Harry Hehl
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.3
Operating system: Linux
Description: select time with time zone not converting
Details:
The "select timestamp with time zone" is working as expected converting the
timestamp to the local time zone.
"select time with time zone" returns the same timezone.
Example:
CREATE TABLE test
(
mytime TIME (6) WITH TIME ZONE,
mytimestamp TIMESTAMP (6) WITH TIME ZONE
);
SET time zone -5;
INSERT INTO test VALUES ('00:00:00', '2006-02-01 00:00:00');
select * from test where mytimestamp = timestamp with time zone '2006-02-01
05:00:00-00';
-- returns inserted row.
select * from test where mytime = time with time zone '05:00:00-00';
-- nothing returned.
select timestamp with time zone '2006-02-01 05:00:00-00';
-- timestamptz
------------------------
-- 2006-02-01 00:00:00-05
--(1 row)
select time with time zone '05:00:00-00';
-- timetz
-------------
-- 05:00:00+00 This should be '00:00:00-05'
--(1 row)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
