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
);