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

Re: Setting timezone: is it bug or intended?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Yura Sokolov <funny(dot)falcon(at)gmail(dot)com>, 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 21:48:50
Message-ID: 22785.1303854530@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Tom Lane wrote:
>> It already is documented.  See
>> http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html#DATATYPE-TIMEZONES
>> specifically the point that POSIX zone names have the opposite sign
>> convention from ISO-8601.
>> 
>> The great thing about standards is there are so many to choose from ;-)

> What isn't documented is why the sign changes for +0300 but not +03:

+03:00 is a legal POSIX zone name (hence the sign is different from SQL
convention).  The other one is evidently being handled by this code path
in check_timezone:

		/*
		 * Try it as a numeric number of hours (possibly fractional).
		 */
		hours = strtod(*newval, &endptr);
		if (endptr != *newval && *endptr == '\0')
		{
			/* Here we change from SQL to Unix sign convention */
			myextra.CTimeZone = -hours * SECS_PER_HOUR;
			myextra.HasCTZSet = true;
		}

which I think is legacy code meant to deal with SQL-standard
specification of timezone offsets as INTERVAL values.  You get the same
interpretation of sign when you use the SQL-spec syntax:

regression=# set time zone interval '+03:00';
SET
regression=# select now();
              now              
-------------------------------
 2011-04-27 00:44:53.560295+03
(1 row)

Like I said, too many standards with their fingers in this pie.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: SuprabhatDate: 2011-04-27 11:55:13
Subject: BUG #5995: connection pooling not working
Previous:From: Bruce MomjianDate: 2011-04-26 21:30:26
Subject: Re: Setting timezone: is it bug or intended?

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