Skip site navigation (1) Skip section navigation (2)

Re: timestamp with time zone

From: Alessandro Gagliardi <alessandro(at)path(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: timestamp with time zone
Date: 2012-02-07 18:32:45
Message-ID: CAAB3BBKckR3D_j==WojuiHgnLB9K2qUv57=b15cx1kNke86Cgw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
> );
>

In response to

pgsql-novice by date

Next:From: Steve CrawfordDate: 2012-02-07 18:55:12
Subject: Re: timestamp with time zone
Previous:From: Alessandro GagliardiDate: 2012-02-07 18:30:13
Subject: timestamp with time zone

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group