Casting and Timestamp

Mark Gibson wrote:

I have unexpected results when trying to cast a string to a timestamp:

test=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00 GMT-5'
test-# ;
timestamptz
------------------------
2006-06-24 20:00:00-05

Seems that what I get is about 10 hours earlier than I expect...

Any ideas why this is happening? Did I assume too much in the cast?

Answered my own question - apparently the 'GMT' portion isn't understood
by postgresql:
template1=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00-05'
template1-#
template1-# ;
timestamptz
------------------------
2006-06-25 06:00:00-05
(1 row)

Mark Gibson <mark@gibsonsoftware.com> writes:

I have unexpected results when trying to cast a string to a timestamp:
test=# select TIMESTAMP WITH TIME ZONE '2006/06/25 06:00:00 GMT-5'
test-# ;
timestamptz
------------------------
2006-06-24 20:00:00-05

IIRC, the semantics of the 'GMT+-x' annotation is defined by a POSIX
standard that has the opposite sign convention to what the SQL committee
adopted. So 'foo-05' in timestamptz output means 'foo GMT+5' in the
POSIX notation (ie, in both cases "5 hours west of Greenwich" is meant).
Yeah, it sucks ... want to arrange a standards-committee shootout?

One reference among many: http://www.twinsun.com/tz/tz-link.htm says

Numeric time zone abbreviations typically count hours east of UTC, e.g.,
+09 for Japan and -10 for Hawaii. However, the POSIX TZ environment
variable uses the opposite convention. For example, one might use
TZ="JST-9" and TZ="HST10" for Japan and Hawaii, respectively.

regards, tom lane