Re: [HACKERS] 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
>