Is Postgres or Ruby adding the timezone to my `timestamp without time zone` column?

If I query my database with SELECT current_setting('TIMEZONE') I get 'UTC' (as expected).

Using PgAdmin, I run the following query:

SELECT foo FROM bar

PgAdmin shows "2011-03-12 08:0开发者_运维问答0:00". However, when I read the value from Ruby (using DataMapper which uses the 'org.postgresql.Driver' JDBC driver as far as I know), it shows "2011-03-12 08:00:00 -0700".

Question: Where in the whole stack is the timezone getting added? Although I realize a lot depends on the specifics of my stack, it would really help to understand what should happen so that I can rule things out. For example, for a timestamp without time zone column, should I expect that JDBC driver gives a 'raw' value with no timezone information?

---

Something in Ruby is making the timezone adjustment:

psql=> select current_setting('timezone');
 current_setting
-----------------
 Canada/Pacific
(1 row)

psql=> select min(created_at) from people;
            min
----------------------------
 2010-07-09 13:58:51.320659
(1 row)

psql=> set timezone = 'utc';
psql=> select current_setting('timezone');
 current_setting
-----------------
 UTC
(1 row)

psql=> select min(created_at) from people;
            min
----------------------------
 2010-07-09 13:58:51.320659
(1 row)

You can check this by doing a raw SQL query of a timestamp from within Ruby and seeing what string you get back.

---

The JDBC driver when reading a timestamp without timezone makes bold/reasonable assumption that this timestamp is expressed in the JVM timezone.

---

If you do not want timezone to be added, use type 'timestamp without timezone'. That way, reader will always read same second/hour/minute/day/month/year as you inserted.

I used following procedure to reproduce that

create table t (
without_tz timestamp  without time zone ,
with_tz timestamp  with time zone 
)
SET SESSION TIME ZONE default;
insert into t VALUES ( now(), now() )
select * from t;
SET SESSION TIME ZONE PST8PDT;
insert into t VALUES ( now(), now() )
select * from t;
SET SESSION TIME ZONE PST6PDT;
insert into t VALUES ( now(), now() )
select * from t;

Observing values from select, I come to conclusion that

• timestamp without timezone is never converted. You read same second/hour/minute/day/month/year what you inserted, no matter what timezone you are in.

  • timestamp with timezone converts values you read to your timezone. they represent same instant (point in time) but hour (and sometimes days, sometimes even minutes) values will be diffrent.