Postgresql Timezone Problem

Trickiness in picking right PostgreSQL schema for timestamp data coming from around the world (varying Time Zones), and how/when to convert it.

Posted to UseNet. Didn't get a response, so tried IRC. Some options considered:

• change current timestamp-with-timezone field to timestamp-without-timezone, converted to client-local-time (from UTC, at insert). Keep UTC field as well for backup.
• keep the current table as is, but make a view that includes the original fields, with the view's version of the timestamp field being a converted timestamp-without-timezone value (I wonder whether I can keep the same field name?). Modify all code to call the view instead of the original value.
• keep the current table as is, but have a stored procedure to get a converted version of the timestamp field. Modify all code to call that converted value instead of the original field

I'm leaning toward the second option at the moment...

• nope, decided that would involve constantly re-converting the data every time you queried on it, which didn't seem very scalable. That would apply to the third option as well.

Update: reference