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...?)