Since we all know that PG stores timestamps in UTC, we have been faced
with the issue of how to reverse the timestamp conversion so that it
will display "properly" for our "clients" about the world.
I have a nice Wenger analog watch that has dual time. I keep the second
movement set to UTC. I'm training myself to think in UTC; and quite
frankly I like it (sorry you can't see the propeller spinning atop my
head). My alternatives are either to buy identical watches for the
hundreds of web client users that I have around the globe, or display
their data in a format that they can see and appreciate.
I have a standard web app, consisting of PHP/Apache/Postgres. Trapping
the timestamps from my clients in UTC is critical to me - because I am
making endurance calculations that will span across DST changes.
However, what is critical to them, is to see what the endpoints were on
the timeline.... hence the need to display (reverse) the timestamps back
to the correct timezone.
It appears that a popular solution is to temporarily change the locale
of the postmaster. I don't see how this could be a reasonable solution,
assuming that once the locale is changed - every client would have a
view of the data with the currently set locale! If this were a PHP or
APACHE session variable rather than a PG locale setting I would be much
happier (btw - is that possible?!).
I'm making a SWAG that my only real solution is to store both the local
timestamp (uncorrected) *and* the (standard) UTC timestamp from my
clients. This way, I can safely calc the endurance using one column,
and report the endpoints using another column.
Anyone see any holes in this? Any other good suggestions that don't
require absurd amounts of complexity?
--
Thanks,
TJ
"The instructions say 'simplify', not 'complicate'. "
- My father, while helping me with my math homework.
[many years... circa 1984]