Skocz do zawartości
Zaloguj się, aby obserwować

[] Luke's Blog - Keep MySQL & PHP time in sync

Polecane posty

A while back, as some of you may or may not recall, I posted a topic about making a line graph of financial transactions and I needed to group transactions together for each plot point depending on the scale being used (scale being used is determined by the start date and the end date chosen - if there are 62 days or less group by day, less 365 group by month, otherwise group by year). I accomplished this using the FROM_UNIXTIME command on the stored UNIX timestamps and grouped by that.

Now this may not be very *efficient* but it works quite well. Especially if I limit the results to be between the start date and end date (should be obvious) so I'm not selecting the entire table. I didn't really like this method at first, so I tried making a "year", "month", and "day" column and did an index in that order. This would be fairly efficient, the problem being that the graph would be stuck in one timezone. This, among other things, bothered me because everything else reflects the users chosen timezone. So I decided to opt for the FROM_UNIXTIME method again.

When I last used this method I had everyone on one time zone: Pacific Standard Time. In later versions of our product we expanded it to be user selectable. What I didn't realize at first (since I don't use these MySQL's time functions that often) is that MySQL has it's own set timezone and that if not set will not match up with what you set in PHP via the 'date_default_timezone_set' function.

To solve this issue I found that you can set the timezone on a per-connection basis, which means that I can have each user set their session to use the correct timezone. I found this in the manual:

SET time_zone = 'America/Los_Angeles';

Which ended up yielding: '#1298 - Unknown or incorrect time zone: 'America/Los_Angeles'

That didn't make very much sense to me, so I did a quick search and found this:

Apparently the only way to make name-matching work is to do this in root terminal:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

Obviously this isn't idea, especially in a framework that you plan on using on multiple servers, dedicated or not. After a bit of trial and error, and with help of the link above, I found this was the most portable method:

$sql = "SET time_zone='".date('P')."';"; // do something with sql

Note: If you are not running at least PHP 5.1.3 you can replace date('P') with substr_replace(date('O'), ':', 3, 0)

So what I end up doing is setting the default time zone of choice from the site's configuration file via 'date_default_timezone_set'. Then when my database class is initialized I run the above query to match up with the configuration file. Then in the user class when the user session is loaded I compare the user's preference against 'date_default_timezone_get()', and if needed set 'date_default_timezone_set' again with the new timezone along with the above query.

As a result the date functions in both MySQL and PHP reflect either the site's configured timezone, or the user's choice.

Zobacz Artykuł w pełnej wersji

Udostępnij ten post

Link to postu
Udostępnij na innych stronach

Bądź aktywny! Zaloguj się lub utwórz konto

Tylko zarejestrowani użytkownicy mogą komentować zawartość tej strony

Zaloguj się, aby obserwować  

  • Kto przegląda   0 użytkowników

    Brak zalogowanych użytkowników przeglądających tę stronę.


Ważne informacje

Kontynuując przeglądanie strony, wyrażasz zgodę na używanie przez nas plików cookies.