Re: 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)