Re: YTA Time Zone Question

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Danny Armstrong <detarmstrong(at)visiontree(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: YTA Time Zone Question
Date: 2007-04-06 17:37:30
Message-ID: 20070406173729.GA83368@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Fuhr 2007-04-06 17:42:41 Re: No of triggers of one single table
Previous Message Tom Lane 2007-04-06 17:21:48 Re: 8.2.3 AutoVacuum not running