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

Re: "timestamp without timezone" and at "time zone"

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Stefan Murphy" <stefan(at)vocalocity(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: "timestamp without timezone" and at "time zone"
Date: 2008-10-17 18:36:58
Message-ID: 24828.1224268618@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"Stefan Murphy" <stefan(at)vocalocity(dot)com> writes:
> I'm seeing some odd behavior (to me) around a timestamp without timezone
> column.  Was hoping someone could shed some light on this for me.  I
> insert current_timestamp into the column.  When I select the value it is
> the expected UTC time.  When I select the value with "AT TIME ZONE EDT"
> I expected it to return in eastern time which is UTC - 4, but is
> returning as UTC + 4.  Is AT TIME ZONE incompatable with this data type?

It's operating as intended, which is not compatible with your
expectation.  AT TIME ZONE on a timestamp WITHOUT tz means "assume that
the given timestamp is local time in this timezone.  Rotate it to UTC
and return that as a timestamp WITH tz".  For display, the result gets
rotated to your display timezone (TimeZone setting), which evidently is
UTC.  So 16:58 in EDT is in fact 20:58 in UTC.

I think the behavior you're after is probably the one associated with
the reverse transform, ie start from a timestamp WITH tz and get one
without.

In general, if you're trying to store a column that represents actual
instants in time, the column type should be timestamp with tz.  When you
use timestamp without tz the interpretation changes depending on the
timezone setting, which is almost surely not what you want.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2008-10-17 18:43:44
Subject: Re: How to transform pg_7.4 function 'to_number' to 8.4 equivalent?
Previous:From: Isabella GhiureaDate: 2008-10-17 18:30:26
Subject: how to see all tables/object in db

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