Postgres Pro
Downloads
Home   >   mailing lists

Thread: Casting and Timestamp

Casting and Timestamp

From
Mark Gibson
Date:
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?

Thanks,
Mark

Re: Casting and Timestamp

From
Mark Gibson
Date:
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)

Re: Casting and Timestamp

From
Tom Lane
Date:
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