Skip site navigation (1) Skip section navigation (2)

Re: Setting timezone: is it bug or intended?

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Yura Sokolov <funny(dot)falcon(at)gmail(dot)com>
Cc: Susanne Ebrecht <susanne(at)2ndQuadrant(dot)com>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Setting timezone: is it bug or intended?
Date: 2011-04-26 18:48:19
Message-ID: 201104261848.p3QImJX06900@momjian.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Yura Sokolov wrote:
> 21.03.2011 15:51, Susanne Ebrecht ?????:
> > Hello Yora Sokolov,
> >
> > Maybe this is not a bug - just an inconsistence:
> >
> > Look here (tested with 9.0):
> >
> > =# set timezone='+3.5';
> > SET
> > =# select current_timestamp;
> >                now
> > ----------------------------------
> >  2011-03-21 16:05:34.761112+03:30
> > (1 row)
> >
> > In any case, this should get documented.
> >
> > Susanne
> >
> >
> My question were about sign of timezone:
> 
> postgres=# set timezone='+03'; -- positive
> postgres=# select now();
> 2011-03-18 11:20:29.696671+03 -- positive
> postgres=# set timezone='+03:00'; -- positive
> 2011-03-18 05:20:35.46008-03 -- negative
> 
> Is it intended?

Uh, it certainly looks very odd.  What I believe is happening is that
+03:00 is processed as a real time zone specification:

	test=> set timezone='+03:00';
	SET
	test=> show timezone;
	 TimeZone
	----------
-->	 +03:00
	(1 row)
	
	test=> select now();
	              now
	-------------------------------
	 2011-04-26 15:41:11.409237-03
	(1 row)

You will notice that because of the SQL standard, GMT+3 processes as -03
utc_offset, and '+03:00' must be processed the same:

	test=> select * from pg_timezone_names;
	               name               | abbrev | utc_offset | is_dst
	----------------------------------+--------+------------+--------
	...
	 Etc/GMT+3                        | GMT+3  | -03:00:00  | f

The '+03' is seen as hours-minutes-seconds:

	test=> set timezone='+03';
	SET
	test=> show timezone;
	 TimeZone
	----------
-->	 03:00:00
	(1 row)
	
	test=> select now();
	              now
	-------------------------------
	 2011-04-26 21:40:57.570654+03
	(1 row)

hence the +03.  Notice how different that looks from the +03:00 above. 

As far as how to even document this, I have no idea.  I think this code
is being processed by the timezone library we include as part of Postgres.
I am lost on how to proceed.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-04-26 20:49:14
Subject: Re: Setting timezone: is it bug or intended?
Previous:From: Michael MusgroveDate: 2011-04-26 11:03:44
Subject: Re: BUG #5297: Add XATMI C API

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group