By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,104 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

massaging data from multiple timezones

P: n/a
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...?)
Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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.
Nov 22 '05 #2

P: n/a
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.
Nov 22 '05 #3

P: n/a
fl*****@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.