timestamp with time zone

From: Alessandro Gagliardi <alessandro(at)path(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: timestamp with time zone
Date: 2012-02-07 18:30:13
Message-ID: CAAB3BB+UdR9V9QoswRNOM8Pnq_hTz=cVqbtXUN4s_ABzfkrSbw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Alessandro Gagliardi 2012-02-07 18:32:45 Re: timestamp with time zone
Previous Message Michael Rowan 2012-02-06 23:13:48 Re: Type mismatch problem