I don’t normally do this kind of thing, but I saw a deal today at MacUpdate Promo, which is 58% off of MAMP Pro. In US dollars, that brings the price from $59 down to $25. If you were ever considering paying for the pro version of MAMP, today’s the day! Unfortunately the offer ends shortly, but hopefully some people will be able to take advantage of it. (And, no, I’m not affiliated with either MacUpdate or MAMP).
Archives For Mac OS X
I’m currently working on the fourth edition of my “PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide” book, due out later this summer. In it, I discuss how to effectively use MySQL’s support for time zones. Specifically, there’s an argument to be made for storing dates using Coordinated Universal Time (strangely enough, abbreviated UTC). UTC is commonly the same time as Greenwich Mean Time (GMT), except during the summer when the UK is an hour ahead of UTC. And that’s the point of using UTC: storing times (and therefore dates) in a format indifferent to any particular time zone. By comparison, my forum software or my blogging software may likely be storing times using the local time zone for the server. That may not seem like a problem until I move the site from one server to another in a another time zone, thereby introducing a disparity in the times. How critical of an issue is that? Well, it depends upon the application, but there’s an easy fix: storing times and dates in UTC.
As I write in the book, three things are required to use UTC. You do not have to change anything about the column that’s storing the date and time, but you do have to change how you INSERT or UPDATE records. Instead of using the NOW() function to get the current timestamp, you’ll want to use UTC_TIMESTAMP(). You can alternatively use UTC_DATE() and UTC_TIME(), to just return the current UTC date or time, respectively. That’s the first step.
Presumably, even though the times are being stored as UTC, you’ll probably want to present them using another time zone. (In the book the specific example converts all times to the user’s selected time zone.) To convert from one time zone to another, use MySQL’s CONVERT_TZ() function. Its first argument is the date and time being converted; the second argument is the original time zone; and, the third argument is the destination time zone. For example, to convert the posted column from UTC to Sydney, Australia’s time zone, you’d use CONVERT_TZ(posted, ‘UTC’, ‘Australia/Sydney’).
The third thing you’ll need to do is install time zone support in MySQL. In other words, the MySQL database needs to be notified of all the time zones that exist and what their offsets are (from UTC). If you don’t take this step, the CONVERT_TZ() function will return NULL values for every conversion. How you install the time zones depends upon your OS. If you’re using Windows, you’ll want to download the time zone description tables from MySQL’s Web site. The download contains the files that correspond to the time zone tables. You just need to shut down MySQL, unzip the downloaded files, and copy/move them to the folder that represents the mysql database.
If you’re using most versions of Unix, including Mac OS X, the operating system itself has time zone definitions built in, and those should be used to populate MySQL’s tables. The most likely location of the system’s time zone files is /usr/share/zoneinfo. After you’ve confirmed the directory exists, you can populate the time zone tables in the database by executing the mysql_tzinfo_to_sql script that comes with MySQL:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
That would be executed from a command line interface, of course, and you’d need to enter the (MySQL) root user password when prompted.
When I executed this command on my Mac, I saw an odd error:
Warning: Unable to load ‘/usr/share/zoneinfo/+VERSION’ as time zone. Skipping it.
What the what? The problem occurred because in the zoneinfo folder, the very first file, named +VERSION, indicated the zoneinfo version. This, of course, was not a time zone file, so when the MySQL script encountered it, it didn’t know how to handle the file and therefore just terminated the import. After deleting (or temporarily moving) the +VERSION file from the zoneinfo directory, I was able to install the time zones in MySQL without a problem. Well, actually, a handful of warnings popped up for a few unusable time zones, but the other 568 time zones imported just fine (yes, who knew there were 568 time zones?).
And that’s all it takes to use UTC in MySQL. But do note that you’ll occasionally need to update the time zone tables, as the particulars of time zones do change, albeit rarely.
For years I was a devotee of Quicksilver, an application launcher and power utility for Mac OS X. And I was not alone: Quicksilver was such an excellent product that the developer got hired by Google to create a similar application for them. Despite Quicksilver’s source code being available, the project languished. Bugs created by the Snow Leopard operating system (or maybe just Leopard) led me, and many others, to finally give up on Quicksilver, begrudgingly, and turn to alternative products (I went back to what I was using before Quicksilver: Launchbar, with which I’m still quite pleased).
Well, I’m thrilled to say that Quicksilver is back! In fact, a new release, b59 went out on April 24th. Quicksilver now has a new Web site, at QSapp.com, and a corresponding blog at LoveQuicksilver.com. Quicksilver is still free, with tons of plug-ins, many of which have been recently updated.
If you’re using a Mac, and aren’t already familiar with Quicksilver, Launchbar, or Alfred, you owe it to yourself to check one or more of them out. It’s totally diminishing to label these applications as just a launchers, although that’s the easiest label to apply. You can launch applications with these programs, or open files, but you can also do thinks such as: perform arithmetic, send emails, move and copy files and folders, and much more.