Mark Gibson <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
