Re: [GENERAL] YTA Time Zone Question
Danny Armstrong <[EMAIL PROTECTED]> writes: > What am I misunderstanding and how do I get it to interpret the value as > utc and then not offset it when I view it, like python and ruby do? What you're misunderstanding is that you messed up the data when you put it into the database, probably by having a timezone setting *at that time* that didn't agree with what your source application expected. If the application sends timestamp strings that don't explicitly include a timezone identifier, PG will assume that they are in the zone identified by TimeZone, and in any case it will then convert the time to UTC for storage. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] YTA Time Zone Question
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:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend