Jump to content
Sign in to follow this  

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

Recommended Posts

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: http://www.electrictoolbox.com/mysql-set-timezone-per-connection/

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

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...

Important Information

W celu świadczenie usług przez nasz Serwis na najwyższym poziomie, w ramach Serwisu wykorzystujemy pliki Cookies (tzw. ciasteczka). Korzystając ze stron Serwisu IPSBEYOND.PL bez zmiany ustawień przeglądarki będą one zapisane w pamięci urządzenia. Jeżeli nie dokonacie Państwo zmiany ustawień przeglądarki internetowej to wyrażacie zgodę na zapisywanie plików Cookies.