Tom,
You said, "It seems to me that you're not entirely understanding how
timestamps work in Postgres." That is an understatement!
Thank you very much for your explanation. I have forwarded it to the
other members of my development group, with my suggestion that we follow
your ideas for future projects. I am not sure how easy it will be to
retrofit existing projects, but I am sure it should be done.
One question: We have customers all over the world. It would be best
if we could rely on the operating system (usually Windows Server 2003)
to tell us what time zone we're in, rather than asking for a specific
timezone when we want to know a wallclock time. Is that possible? If
not, it's not that big a deal because our database includes a table
named system_info that contains a single record describing the
customer's environment. We could just add a timezone field to that
table. But how would we do that? What data type should that column
have, and what would a query look like that converts a time from UTC to
local time based on that field?
As I was typing that question, I think I came up with the answer: the
question is irrelevant. The reason for having a field to store times in
UTC is so that intervals between times can be calculated without
worrying about daylight savings time. But Postgres will take the
timezone into account when calculating intervals, so there is no reason
at all to store a UTC version of the time.
And, as you pointed out, storing the same value twice is horrible
database design.
RobR