From: Curt Sampson <cjs@...>
Date: 2004-11-17T15:11:21+09:00
Subject: DBI and timestamp with time zone


I use timestamps with time zones extensively in PostgreSQL.
Unfortunately, it's looking to me as if neither the DBI nor the Pg
driver support time zone information. Even worse yet, it appears that
they're quite actively ignoring the time zone:

irb(main):034:0> dbh.do("set time zone 'Asia/Tokyo'")
=> 0
irb(main):035:0> dbh.select_one('select registration_date from member')
=> [#<DBI::Timestamp:0x82a2b70 @hour=12, @month=7, @original_time=Sat Jul 14 12:35:45 JST 2001, @second=45, @year=2001, @fraction=nil, @minute=35, @day=14>]

irb(main):036:0> dbh.do("set time zone 'US/Eastern'")
=> 0
irb(main):037:0> dbh.select_one('select registration_date from member')
=> [#<DBI::Timestamp:0x82966f4 @hour=12, @month=7, @original_time=Sat Jul 14 12:35:45 JST 2001, @second=45, @year=2001, @fraction=nil, @minute=35, @day=14>]

This is probably because the date comes back from postgres as
"2001-07-14 12:35:45+09" and "2001-07-13 23:35:45-04", respectively, for
the two above instances, and ParseDate is used to parse it, understands
the time zone, but then converts it to local time. (Just a quick
guess--I've not followed this all the way through.)

So how can I get working time zone support in DBI with Postgres?

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.NetBSD.org
     Make up enjoying your city life...produced by BIC CAMERA