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

Re: Timestamp confusion

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Rob Richardson <Rob(dot)Richardson(at)rad-con(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Timestamp confusion
Date: 2010-09-28 15:21:29
Message-ID: AANLkTinV3tMfRU3RQZNZ3+JN2iiB8n332JNdVSbTRt5i@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Tue, Sep 28, 2010 at 11:00 AM, Rob Richardson
<Rob(dot)Richardson(at)rad-con(dot)com> wrote:

> I need to know if 2010-09-28
> 13:09:27.015-04 from a timestamp with time zone column represents the
> same time as 2010-09-28 13:09:27.015 from a timestamp without time zone
> column.

To answer this question, try:

SELECT '2010-09-28 13:09:27.015'::timestamp = '2010-09-28
13:09:27.015-04'::timestamp with time zone;

You should see that Postgres assumes the plain timestamp is entered in
a timezone which comes from the "timezone" GUC (again, use SHOW
timezone; to see this). If you have your timezone set to US/Eastern,
the above comparison will return true. If you have it set to something
else, it should return false. If you use "timestamp with time zone"
type everywhere, you'll avoid potential headaches of keeping track of
timezones.

Josh

In response to

pgsql-novice by date

Next:From: Kasia TuszynskaDate: 2010-09-28 19:48:44
Subject: Re: PostgreSQL 8.4 won't start
Previous:From: Tom LaneDate: 2010-09-28 15:05:09
Subject: Re: Timestamp confusion

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