Thread: timezone locale fun

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]

TJ wrote:

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

Yeah- this sort of thing /is/ fun, isn't it? :-) Frankly I don't think
that there's a "correct" solution (look as the vituperation surrounding
MS timezone handling) and the more info you store when the data is saved
the better since that gives you the option of going back and re-running
reports if somebody tells you the goalposts have moved. I store all data
in GMT with info in each record so that it's traceable, gross timezones
can obviously be handled easily in the query but the final application
of DST correction is handled in the client apps (written in Delphi but
using embedded scripting and interpolation).

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or
colleagues]

TJ wrote:

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

How about using a PHP session variable to store the timezone of the
client, and then performing selects on the data using:

SELECT field AT TIME ZONE '$client_timezone' FROM table

Jochem