Thread: YTA Time Zone Question

Hi,
I have a question regarding postgres 8.2 handling of timezones.

I receive posts of unix timestamps and convert them and save them to a
timestamptz(0). I've read the docs on this, timestamptz stores
internally as utc. The date is formatted per local time on display.

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.

If I set time zone -7, then I get "2007-03-24 21:52:16-07". (set time
zone 'PDT' returns unrecognized time zone.)

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?

Any help is appreciated,
Danny

Danny Armstrong <detarmstrong@visiontree.com> 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

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

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