Thread: BUG #2661: select time with time zone not converting
The following bug has been logged online:
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)
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)