Thanks very much, my issue was addressed exactly.
My implicit assumption that inserts of timestamps are not formatted by
local time (db time zone setting) in the absence of an explicit time
zone identifier was incorrect. Ok, it seems obvious now. TIMESTAMPTZ is
your friend.
The solution is to rewrite my function that converts the unix timestamp:
SELECT TIMESTAMPtz 'epoch' + INTERVAL '1170728520 seconds';
-Danny
-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Friday, April 06, 2007 9:38 AM
To: Danny E. Armstrong
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] YTA Time Zone Question
On Thu, Apr 05, 2007 at 05:52:02PM -0700, Danny Armstrong wrote:
> If ruby and python tell me the value I just inserted into the db,
> 1174773136, is Sat Mar 24 21:52:16 UTC 2007, then I expect that
>
> set time zone 0; -- format as though I'm in utc
> select measurement_time
> from table
>
> will also yield that time.
>
> Instead it yields "2007-03-25 04:52:16+00", which means it interprets
> the original value as local time (I'm PDT), and then formats it as UTC
> by adding 7 hours to it.
Turn on statement logging and see what the Ruby and Python drivers
are executing. Here's an example Ruby script:
require 'dbi'
require 'time'
t = Time.parse('Sat Mar 24 21:52:16 UTC 2007')
dbh = DBI.connect('dbi:Pg:dbname=test', 'user', 'password')
dbh.do('INSERT INTO foo (t1, t2) VALUES (?, ?)', t, t.to_s)
dbh.disconnect
When I run this script the database logs the following:
INSERT INTO foo (t1, t2) VALUES ('2007-03-24 21:52:16', 'Sat Mar 24
21:52:16 UTC 2007')
Notice that the first value (of class Time) is sent without a
timezone; the database therefore interprets it according to the
database's timezone setting (US/Pacific in my test environment).
The second value (of type String) includes the timezone so the
database interprets it as expected.
test=> SET timezone TO 'UTC';
test=> SELECT t1, t2 FROM foo;
t1 | t2
------------------------+------------------------
2007-03-25 04:52:16+00 | 2007-03-24 21:52:16+00
(1 row)
test=> SET timezone TO 'US/Pacific';
test=> SELECT t1, t2 FROM foo;
t1 | t2
------------------------+------------------------
2007-03-24 21:52:16-07 | 2007-03-24 14:52:16-07
(1 row)
The Python driver you're using might behave the same way. I'd
suggest contacting driver authors.
--
Michael Fuhr
