Re: 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