Search Postgresql Archives

Re: YTA Time Zone Question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux