Re: Timestamp confusion

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