BUG #2661: select time with time zone not converting

On Sep 28, 2006, at 3:54 PM, Harry Hehl wrote:

Bug reference: 2661
Logged by: Harry Hehl
Email address: harry.hehl@diskstream.com
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)

Confirmed in 8.1.4 and HEAD, though I'm not sure what date it's from
since I'm on a plane right now (is there any way to get that info out
of the local repository?)
--
Jim Nasby jimn@enterprisedb.com
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Thu, Oct 05, 2006 at 09:04:50PM -0500, Jim Nasby wrote:

On Sep 28, 2006, at 3:54 PM, Harry Hehl wrote:

Bug reference: 2661
Logged by: Harry Hehl
Email address: harry.hehl@diskstream.com
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)

Actually, this is expected behavior. Unlike timestamptz, timetz actually
stores the original timezone info.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)