I'm having trouble with the interaction between postgres 7.4, DateTime::Format::Pg, and DateTime::TimeZone::offset_as_seconds. I'm somewhat confused as to which piece of software should be altered to handle this pathological case, so I thought I'd put it here for consideration and comment.
Postgres returns the following for "timestamp with time zone"-type data (in ISO datestyle mode):
2004-05-09 01:37:50-07
This gets handed off to the DateTime::Format::Pg::parse_timestamptz (Pg is for postgres), which runs it through this regex to extract the fields:
/^(\d{4,})-(\d{2,})-(\d{2,}) (\d{2,}):(\d{2,}):(\d{2,})(\.\d+)?( BC)? *([-\+][\d:]+)?$/
This regex maps the string to the following fields:
year: 2004 month: 05 day: 09 hour: 01 minute: 37 second: 50 nanosecond: era: time_zone: -07
This data tries to get mangled into a datetime. The time_zone field gets passed around a bit, and eventually into DateTime::TimeZone->offset_as_seconds(), which promptly gives up on the offset because it doesn't have 4 digits.
Now, the date coming out of postgres _looks_ like it is conforming to ISO 8601. DateTime::Format::Pg seems to be cool with it, too. However, the docs on DateTime::TimeZone::offset_as_seconds are pretty clear that this input will be rejected.
A couple possible explanations:
• Did DateTime::TimeZone::offset_as_seconds at some point allow 2 digit offsets? (i can't find any evidence of this...)
• Did postgres at some point output data with 4 (or 6) digit offsets? (Not that I can find or remember)
• Has DateTime::Format::Pg::parse_timestamptz never worked? (one would hope not, but....)
A couple possible fixes:
• Alter DateTime::TimeZone::offset_as_seconds to accept 2 digit offsets. This seems the easiest solution, and will add this capability to all of the DateTime modules.
• Alter DateTime::Format::Pg to pad the offset digits coming in from postgres. This is also fairly good, though the code would be duplicated in DT::Fmt::ISO8601 (once the date + time parsing code is written).
• Alter my DBI layer to pad the timestamps coming out of postgres to 4 digit offsets before handing them to DateTime::Format::Pg::parse_timestamptz.
Comments/suggestions? I'm happy to write the code... you guys just tell me where it should go, and I'll send you patch files and unit tests.
-Jonah
