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
