Re: Casting and Timestamp

Mark Gibson <mark(at)gibsonsoftware(dot)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