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...?)
To clarify some of the apps, they do things like
* summarize number of events in each hour-window (2am-3am)
* list the time that certain specific events occur
In these cases you want to report based on the time local to where the
events take place, not in GMT.
A compromise approach I'm considering is
* keeping the current timestamp-with-timezone field type,
* making a stored procedure localEventTime() which converts the
timestamp to source-local-time
* replacing all the occurences in the various apps that point to
event_time with localEventTime()
I'm not thrilled by this because I'm afraid (a) I'll still have to
review every dang query by hand to make sure it makes sense, and (b)
someone sometime will forget to use the stored procedure.
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