Re: timestamp with time zone

Resending as I'm not sure it worked the first time:

On Tue, Feb 7, 2012 at 10:30 AM, Alessandro Gagliardi
<alessandro(at)path(dot)com>wrote:

> I'm trying to insert a timestamp with a time zone, the time zone is
> automatically set to the time zone of the server, not the time zone I
> specify in my statement. For example, if I try:
> INSERT INTO blocks ("moment_type", "user_id", "block_id", "created")
> VALUES
> ('thought','4d0b983456a35863fe000bec','4f31670df1f70e6cc2000ac3','2012-02-07T21:01:49.329590+03:00'::timestamptz);
> and then:
> SELECT created FROM blocks WHERE block_id = '4f31670df1f70e6cc2000ac3';
> I get "2012-02-07 10:01:49.32959-08". Incidentally 2012-02-07
> 10:01:49.32959-08 is the same time as 2012-02-07T21:01:49.329590+03:00 so
> it's not like it's ignoring the time zone information. But I need to be
> able to query based on the local time of the user, not the server, so
> resolving 2012-02-07T21:01:49.329590+03:00 as 2012-02-07 10:01:49.32959-08
> is not useful to me.
> I feel like there must be something stupid I'm doing wrong. Of course I
> could store the time zone separately and then make the adjustment on the
> other end, but that would seem to defeat the purpose of having a TIMESTAMP
> WITH TIME ZONE data type.
>
> Thanks,
> -Alessandro
>
> P.S. FWIW, here is my table definition:
> CREATE TABLE blocks
> (
> block_id character(24) NOT NULL,
> user_id character(24) NOT NULL,
> created timestamp with time zone,
> locale character varying,
> shared boolean,
> private boolean,
> moment_type character varying NOT NULL,
> user_agent character varying,
> inserted timestamp without time zone NOT NULL DEFAULT now(),
> networks character varying[],
> lnglat point,
> CONSTRAINT blocks_pkey PRIMARY KEY (block_id )
> )
> WITH (
> OIDS=FALSE
> );
>