Brent,
> What's the best way to timestamp records if records are to be exported
and
> then re-imported to another web server in a different time zone? Data
will
> be exported as comma delimited data. All of the combined records
should
> reflect the same instant in time, and not have the web site in
California
> to be timestamping data 3 hours earlier than New York. If the data
were to
> be merged from both sites, then records added to CA and NY at the same
> instant should have the same time stamp.
>
> The data will originate from a PHP webpage form so I was thinking of
using
> PHP's gmdate function to get the GMT and store that in the table. So
all of
> the dates and times in the database are GMT. When the user displays
the
> data from the table I have to format the date to a single timezone
(like
> Eastern Time) when the user displays the data in the browser. I don't
> really care what timezone it gets displayed as, as long as sorting the
> records by date show NY and CA records together if they were inserted
at
> the same instant.
>
> I'd prefer to use a MySQL function to get the GMT because I don't want
to
> restrict data input to using just PHP. Does MySQL have a Now("GMT")
option?
> Or is there a better way to handle this?
AFAIK all datetime data stored in MySQL is inherently timezone agnostic.
However if the time is taken from the server running MySQL, eg
auto-UPDATE/INSERT into a timestamp field, then the time recorded will
be localised BUT will not be labeled as such!
To answer your last question first: there are no GMT options/time
functions (again AFAIK) in MySQL.
To solve exactly this problem, I do what you have outlined in paragraph
two - use PHP to pre-process all times, convert them to UTC, and
explicitly store those values into MySQL.
When it comes time to retrieve the data then you have a problem!
Obviously you can train your people to think in UTC - just as long as
they don't keep comparing that against their wrist-watches. You could
also keep one time in the db and yet endeavor to display two or even
three where appropriate (UTC, time at display location, and time at
location that stored data) If you interrogate the retrieving server it
will advise PHP in which timezone it has been located, and thus a simple
addition/subtraction will allow UTC datetimes to be 'adjusted' to the
local time (this breaks down big-time if the people in Denver are
accessing a server in California!*** Because all datetimes in the db are
UTC, there is no problem about maintaining comparative chronology around
the world!
Your other choice, of course, is to run everything as local time, and
then to post-process the transported .CSV files. I suspect that this
will involve more manual effort on an operational basis (in which case,
Murphy rules!), whereas the above can put all the thought/confusing
calculations (including Summer time issues) onto the computer's
shoulders!
*** for the benefit of enquiring minds: the two places are in separate
time zones!
Please call back if I haven't addressed/anticipated all of your issues,
=dn
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
