Re: Casting and Timestamp

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

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