List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:March 30 2010 1:05am
Subject:RE: timezone questions
View as plain text  
> -----Original Message-----
> From: Elim PDT [mailto:elim@stripped] 
> Sent: Monday, March 29, 2010 5:57 PM
> To: mysql@stripped
> Subject: timezone questions
> data from one server in timezone A ported to another server 
> in timezone B, what will happen for the records with datetime columns?

If you have a timezone stored on your server, then all dates/times are
stored relative to that. If you now change it, say you were in CA and moved
to NY, your times retrieved are now actually 3 hours off technically
depending. Even if you set your new timezone to NY. mySQL doesn't store the
timezone you started with. It just stores that you created the record at
noon let's say. That noon is the noon where the server timezone is. This
may or may not be an issue for you depending on your application. If you're
moving an entire company and this is a local database with respect to the
company (say, a time-off tracker, or payroll or something) you may not
care. All you care is that at 3pm some user asked for time off. And since
you all now live in NY, it's your 3pm local time. 

Now, having said that, this in most cases is a huge deal since the web is
global and rarely do web pages live in such a local setting. More often,
there are people from different time zones accessing them.

> What is the recommended way of handling this kind of issues? Thanks 

Real sites set their timezone to UTC and store everything in the database
as UTC as well.

Then you adjust the time on the client based upon their local timezone.

Both PHP and mySQL (and other languages) have functions to convert based
upon the TZ variable.


Importing files as elementsDemetris27 Mar
  • mysqld_safeCharles Brown29 Mar
    • Re: mysqld_safeGlyn Astill29 Mar
      • Re: mysqld_safeColin Streicher29 Mar
        • Re: mysqld_safeEric Bergen18 Apr
      • timezone questionsElim PDT30 Mar
        • RE: timezone questionsDaevid Vincent30 Mar