Thread: massaging data from multiple timezones

I inherited a system which I need to now extend.

It's like a security system, collecting event times from many sources.

When the system was built all the sources were local, so timezones
didn't matter. But now we're working in more areas, so they do.

The raw data comes in as UTC, and the current code converts it to
server-local time and inserts it into the database (the event_time
field is type 'timestamp').

But almost all the reporting should be "thinking" in the *source's*
timezone. Rather than having to alter 50 different programs that touch
the data, it seems more sensible to me to have that initial data table
store the data in source-local time.

Is such an approach possible? How? Do I need to convert that
event_time field to be timestamp-without-timezone?

(Or am I going in a bad direction...?)

fluxent@yahoo.com (Bill Seitz) writes:
> The raw data comes in as UTC, and the current code converts it to
> server-local time and inserts it into the database (the event_time
> field is type 'timestamp').

> But almost all the reporting should be "thinking" in the *source's*
> timezone. Rather than having to alter 50 different programs that touch
> the data, it seems more sensible to me to have that initial data table
> store the data in source-local time.

Why don't you store it in UTC and read out in whatever timezone you
want?  That is what the Postgres timestamptz type is designed to do...

            regards, tom lane