Currently, the extract(timezone_hour ...) and extract(timezone_minute
...) constructs (also the equivalent date_part() calls) return positive
values for timezones west of Greenwich, and negative values for
timezones east of Greenwich.

While the SQL92 spec was quite vague on the subject of the signs of
timezone displacements, SQL99 seems to be pretty clear that

         Local time is equal to UTC (Coordinated Universal Time) plus
         the time zone displacement,

which would mean that positive displacements correspond to zones east of
Greenwich.  Another point in favor of this interpretation is that the
spec defines the legal range of displacement as -12:59 to +13:00, which
is clearly intended to accommodate New Zealand Daylight Time (13 hours
ahead of UTC) ... so NZDT has to be a positive offset not a negative one.

Interestingly, this is also the sign convention used by the timestamptz
and timetz I/O routines, which are certainly much more heavily used than
EXTRACT().  The only other place I can find that uses west-is-positive
convention is the code for SET TIMEZONE with a direct numeric timezone
offset.

I think we got this wrong as a result of misreading SQL92, and we ought
to change EXTRACT() and SET/SHOW TIMEZONE to use the same sign
convention as timestamp input/display use.

Comments?  Can anyone confirm which sign is used by other DBMSes?

			regards, tom lane

Thu, 03 Jul 2003 13:18:09 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Are we backwards on the sign of timezones?
On 3 Jul 2003 at 13:18, Tom Lane wrote:

> Comments?

The message headers for this email contained:

Received: from www.postgresql.com (www.postgresql.com 
[64.117.225.209])
	by m20.unixathome.org (Postfix) with ESMTP id 77EC27A69
	for <dan@langille.org>; Thu,  3 Jul 2003 15:33:13 -0400 (EDT)


My mail server is m20.unixathome.org and yes, it's four hours behind 
GMT.  My PG server in Vancouver, shows this:

# select current_time;
       timetz
--------------------
 12:36:43.579071-07

Which is three hours west of here (Ottawa).    I was going to check 
my NZ server, but it's in the process of upgrading PG..


-- 
Dan Langille : http://www.langille.org/


Thu, 03 Jul 2003 15:45:12 -0400
"Dan Langille" <dan@langille.org>
Re: Are we backwards on the sign of timezones?
The date / time of your message at the top of my email client was:

Thu, 03 Jul 2003 13:18:09 -0400

And most of my stuff is -0600 or -0700 and I live in Colorado.  Every 
instance I've seen that shows the correction for me has been a -0600/-0700 
depending on daylight savings.

So, it looks like the standard for email / system time.

I use timestamptz

On Thu, 3 Jul 2003, Tom Lane wrote:

> Currently, the extract(timezone_hour ...) and extract(timezone_minute
> ...) constructs (also the equivalent date_part() calls) return positive
> values for timezones west of Greenwich, and negative values for
> timezones east of Greenwich.
> 
> While the SQL92 spec was quite vague on the subject of the signs of
> timezone displacements, SQL99 seems to be pretty clear that
> 
>          Local time is equal to UTC (Coordinated Universal Time) plus
>          the time zone displacement,
> 
> which would mean that positive displacements correspond to zones east of
> Greenwich.  Another point in favor of this interpretation is that the
> spec defines the legal range of displacement as -12:59 to +13:00, which
> is clearly intended to accommodate New Zealand Daylight Time (13 hours
> ahead of UTC) ... so NZDT has to be a positive offset not a negative one.
> 
> Interestingly, this is also the sign convention used by the timestamptz
> and timetz I/O routines, which are certainly much more heavily used than
> EXTRACT().  The only other place I can find that uses west-is-positive
> convention is the code for SET TIMEZONE with a direct numeric timezone
> offset.
> 
> I think we got this wrong as a result of misreading SQL92, and we ought
> to change EXTRACT() and SET/SHOW TIMEZONE to use the same sign
> convention as timestamp input/display use.
> 
> Comments?  Can anyone confirm which sign is used by other DBMSes?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 


Thu, 3 Jul 2003 13:56:08 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: [HACKERS] Are we backwards on the sign of timezones?

I think you are right about this.  When people look up a
timezone, what we see is the offset to UTC.  
UTC + (offset) should equal current time.  

We should be consistent.  And since the timestamp with
timezone is relatively recent, maybe it will not be so painful
to change the behaviour of extract() and date_part() to be
consistent with the display of timezones.  I don't think that
many people do use extract or date part to fuss with the
timezone.  But that is just my experience.

Anyone else who uses extract() and date_part() with timezones
should weigh in their opinions.

elein

On Thursday 03 July 2003 10:18, Tom Lane wrote:
> Currently, the extract(timezone_hour ...) and extract(timezone_minute
> ...) constructs (also the equivalent date_part() calls) return positive
> values for timezones west of Greenwich, and negative values for
> timezones east of Greenwich.
> 
> While the SQL92 spec was quite vague on the subject of the signs of
> timezone displacements, SQL99 seems to be pretty clear that
> 
>          Local time is equal to UTC (Coordinated Universal Time) plus
>          the time zone displacement,
> 
> which would mean that positive displacements correspond to zones east of
> Greenwich.  Another point in favor of this interpretation is that the
> spec defines the legal range of displacement as -12:59 to +13:00, which
> is clearly intended to accommodate New Zealand Daylight Time (13 hours
> ahead of UTC) ... so NZDT has to be a positive offset not a negative one.
> 
> Interestingly, this is also the sign convention used by the timestamptz
> and timetz I/O routines, which are certainly much more heavily used than
> EXTRACT().  The only other place I can find that uses west-is-positive
> convention is the code for SET TIMEZONE with a direct numeric timezone
> offset.
> 
> I think we got this wrong as a result of misreading SQL92, and we ought
> to change EXTRACT() and SET/SHOW TIMEZONE to use the same sign
> convention as timestamp input/display use.
> 
> Comments?  Can anyone confirm which sign is used by other DBMSes?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 
> 

-- 
=============================================================
elein@varlena.com     Database Consulting     www.varlena.com
PostgreSQL General Bits    http:/www.varlena.com/GeneralBits/
   "Free your mind the rest will follow" -- en vogue


Thu, 3 Jul 2003 12:56:43 -0700
elein <elein@varlena.com>
Re: Are we backwards on the sign of timezones?
On 3 Jul 2003 at 13:18, Tom Lane wrote:

> Comments?

Now that my NZ server is up and running:

template1=# select now();
              now
-------------------------------
 2003-07-05 12:47:15.444535+12

That doesn't look backwards to me.  Perhaps I don't understand the 
problem.  After rereading your original post:

         Local time is equal to UTC (Coordinated Universal Time) plus
         the time zone displacement,

In the above, the local time is  2003-07-05 12:47:15.444535.  UTC 
would be  2003-07-05 00:47:15.444535.  To which we add +12 hours to 
get local time.  That appears to be consistent with the SQL99 spec.
-- 
Dan Langille : http://www.langille.org/


Fri, 04 Jul 2003 20:58:17 -0400
"Dan Langille" <dan@langille.org>
Re: Are we backwards on the sign of timezones?
"Dan Langille" <dan@langille.org> writes:
> Now that my NZ server is up and running:
> template1=# select now();
>  2003-07-05 12:47:15.444535+12

> That doesn't look backwards to me.

Try EXTRACT(timezone_hour from now());

The timestamp I/O routines are using what I think is the correct sign.
EXTRACT() is at variance.  So is SET TIMEZONE with a numeric offset.

			regards, tom lane

Fri, 04 Jul 2003 23:22:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Are we backwards on the sign of timezones? 
On 4 Jul 2003 at 23:22, Tom Lane wrote:

> "Dan Langille" <dan@langille.org> writes:
> > Now that my NZ server is up and running:
> > template1=# select now();
> >  2003-07-05 12:47:15.444535+12
> 
> > That doesn't look backwards to me.
> 
> Try EXTRACT(timezone_hour from now());
> 
> The timestamp I/O routines are using what I think is the correct sign.
> EXTRACT() is at variance.  So is SET TIMEZONE with a numeric offset.

select now(), extract(timezone_hour from now());
              now              | date_part
-------------------------------+-----------
 2003-07-05 23:15:09.760771+12 |       -12

Yep, I'd say that should return +12, not -12.
-- 
Dan Langille : http://www.langille.org/


Sat, 05 Jul 2003 07:16:23 -0400
"Dan Langille" <dan@langille.org>
Re: Are we backwards on the sign of timezones? 