Category Archives: Xataface

Using a “Silent” Java Applet to Render PDFs in HTML

I have read many obituaries for the Java applet. Once upon a time, in the late 1990’s the applet was king of the internet and hope for the future of rich web-based applications. Its decline and (almost) demise can be attributed to a number of factors, not the least of which is the fact that HTML and Javascript have improved to the point where most of the the functions that used to require an applet can now be performed directly in the web browser without requiring any third-party plugins. Nowadays, you scarcely run across any websites that use applets, and when you do, they generally stick out like a sore thumb. They are generally slow to load, and they almost always pop up with a security dialog asking you to approve their execution based on a signed certificate.

Signed Applets – Full Access or No Access

This clumsy security model is the other reason for the growing irrelevance of applets. An applet can only run in two different modes:

  1. Run inside the “sandbox”. The applet cannot access any system information from the client computer, cannot access the file system, and cannot make any network connections to any hosts other than where the applet was loaded from.
  2. Signed Applet – Full access. The applet runs with full permissions of the user. E.g. it can connect to any host, access any client system information, and access the hard drive. This level of access is very risky for the user especially if the applet is just running inside some random webpage that they are trying to open. The applet has the ability to delete the user’s hard drive or copy files from their hard drive without the user even knowing.

Because applet developers are so limited with the sandbox model, most developers end up distributing their applets as signed applets so that they can be run on clients’ computers with full access privileges. This works out great if the client fully trusts the applet developer – but that isn’t the typical client/developer relationship. Most users just click “okay” whenever these types of security dialogs pop up, so it becomes very easy for malicious developers to create trojans for unsuspecting users who browse to their web pages.

Since the proliferation of signed applets across the internet would result in a very dangerous browsing experience for the average internet user, we can be thankful that the web has opted for the applet-less direction that instead makes use of HTML and Javascript – which work inside a secure sandbox. Signed applets are now generally reserved for B2B applications and in-house apps where the client computer has a trusting relationship with the developer.

Unsigned Applets Are Still Safe

All that said, unsigned applets that run inside the applet sandbox are safe for a client to run since they don’t provide any access to the client’s computer. Unsigned applets’ fade from prominence is the natural result of improvements to Javascript and HTML. Javascript/HTML/CSS now have the tools required to create complex user interfaces with rich client interaction. It is no longer necessary to embed a Swing-based user interface inside the browser to give the user a rich experience. Further, it is inconvenient to work with a hybrid of technologies so the developers will often steer clear of applets entirely – opting for pure javascript solutions. For many years, applets were still necessary to accomplish things like drag-and-drop from the desktop (though that required a signed applet), or some more complex user interface widgets, but HTML 5 has introduced most of the tools to do all of these things inside Javascript natively. So the applet has been even further crowded out.

So What Are Applets Useful For Now?

The applet’s relevance may have been eroded down to a few grains of sand, but it turns out that some of those grains still shine like diamonds. Javascript can do many things well, but there are still a few items that Java can bring to the party. For example:

  1. Javascript doesn’t handle processor intensive operations well. It runs in a single thread so any intensive operations will make the browser hang and possibly even crash. Java, on the other hand, is multi-threaded so it can perform parallel processing in the background and pass results back to the browser in way that doesn’t interfere with the user experience.
  2. Java provides a rich, almost limitless set of libraries. – Hence you can find a library to do just about anything you need. The provides an alternative to making server-side requests for processing using AJAX. Instead requests can be passed to a “silent” applet that runs on the client computer – saving both network usage and server usage.

The “Silent” Applet

Observing the strengths of Java vs Javascript yields an interesting model for making use of applets in a web page. I call this the “silent” applet because it is designed to be completely transparent to both the Javascript developer, and the user. A silent applet is one that is loaded silently in the web page and does not manifest itself visually in the web page. Rather is runs invisibly along side the web page as a daemon waiting for requests from the web page which it processes and returns to the web page. This is similar to the AJAX model where the client sends background requests to the server, and the server returns a response which the client then processes. With this model, the request is not sent to the server at all. This saves both network and server resources since much more processing can take place on the client.

The silent applet is useful in situations where a significant amount of processing is required so that performing the “calculation” inside javascript is either impossible or would be disruptive to the user experience. This might include rendering a PDF page as an image, generating a complex chart, performing an image, video, or sound transformation, etc….

Example Silent Applet: Web PDF Renderer

I am developing a PDF reporting module for Xataface. I want the UI to allow the user to arrange database fields and content over top of an existing PDF that they upload as a means of creating report templates that can then be rendered as full reports later on. For this I need to be able to display the PDF as a background image in the user’s work space. I also need the user to be able to navigate through the PDF to different pages and to zoom in and out. The current state of Javascript and HTML is such that the rendering of PDFs needs to be relegated to a different system. Either I need to render the PDF pages as images on the server-side, or I need to find a way to do it in the client.

Rendering the PDFs on the server side would require me to install some server-side extensions or applications that can handle PDFs. There are many, but my goal is to keep the server requirements minimal so that it will work inside a standard LAMP install. So my preference is to find a way to render the PDF on the client side. There are various flash and Java applets already that display a PDF inside the browser, however I’m concerned with mixing flash or applets into the UI of my editor as it will likely result in painful, or intermittent conflicts down the road as the technologies choose not to place nicely together. So I decided to create a silent applet for rendering the PDF.

The Web PDF Renderer applet runs as a silent daemon in the background. There is a thin javascript API that can be used to ask the applet to render pages of a PDF at various sizes. When the processing is done, the applet calls a javascript callback to update an <img> tag in the user interface. This allows me to keep the entire user interface in standard HTML and manipulated via the DOM.

Using the Library

One example use of the library is to render the PDF to an HTML <img> tag. The following example creates a new PDFPage wrapper (which creates an img tag), then appends the image to the body of the document, and finally renders the PDF page. The render() method sends a signal to the applet to render the PDF page. When the applet is complete, it sends the data back the PDFPage object which updates the image source with the new data.

        // Short reference to PDFPage constructor
        var PDFPage = xataface.modules.pdfreports.PDFPage;
        
        // Create a new page  (first page of document)
        var page = new PDFPage({
            width: 800,
            url: 'test.pdf',
            page: 0     
        });
        
        // Append page’s <img < tag to the document body
        $(‘body’).append(page.el); 
        
        // Render the page  (done asynchronously)
        page.render();
    

Inside the Box: The implementation

We need to set up a mechanism for passing messages back and forth from Javascript to the applet and back. For javascript to java communication it is as easy as calling public methods defined on the applet directly. These are exposed and callable in Javascript. For the reverse, we make use JSObject which is available standard as part of the Java plugin. There is quite a bit of information on Java-Javascript communication on Oracle’s website.

We created a global queue that is used to pass the messages:

PDFPage.queue = [];

Note that is is just an empty array at this point.

Calling the render() method will pass a message to the applet – it essentially works as follows (this has been simplified for the example, but gets the point of strategy across):

function PDFPage_render(){
    
    PDFPage.queue.push(this);
    startDispatch();
        
}

So render a page involves just two things:
1. We push the PDFPage object onto the message queue so that the applet can access it.
2. We call startDispatch(). This function essentially tells the applet that the queue has been updated so it can start processing.

The startDispatch() function up-close:

function startDispatch(){
        var applet = $('applet[name="'+PDFPage.appletID+'"]').get(0);
        if ( !applet ){
             var attributes = {
                name: PDFPage.appletID,
                code:       "com.xataface.modules.pdfreports.PDFRendererApplet",
               codebase: PDFPage.codebase,
                archive:    "WebPDFRenderer.jar, commons-codec-1.5.jar, commons-logging-1.1.1.jar, icepdf-core.jar",
                width:      1,
                height:     1
            };
            var parameters = {
                startDispatch:"PDFPage.startDispatch()",      
                queue:"PDFPage.queue"
            }; 
            var version = "1.5"; 
            deployJava.runApplet(attributes, parameters, version);
            
            
        } else {
        
            try {
                applet.startDispatch();
            } catch (e){
                setTimeout(function(){
                    startDispatch();
                }, 1000);
            }
        }
    
    }

At its core this is just a wrapper around the applet’s startDispatch() method – but it needs to handle some edge cases to load the applet the first time it is called. If the applet hasn’t yet been added to the dom, it adds it and tries to call itself again. If the applet is there but not loaded yet, it waits 1 second and tries again.

The deployJava.run() method is from the standard Java deployment code available here. Notice that we pass 2 parameters to the applet when we load it:

  1. We pass the javascript path to the queue so that the applet knows where to find its message queue.
  2. We pass the startDispatch() method call so that the applet is able to call its own startDispatch() method through javascript.

Inside the applet:

The start() method just loads the parameters that we passed, then calls the startDispatch() method for the first time:

public void start(){
        
        queue = (JSObject)JSObject.getWindow(this).eval(this.getParameter("queue"));
        JSObject.getWindow(this).eval(this.getParameter("startDispatch"));
    }

The guts can be found in the startDispath() method:

public synchronized void startDispatch(){
        
        if ( running ) return;
        running = true;
        
        
        Thread dispatcher = new Thread(new Runnable(){

            public void run() {
                while (!stopDispatcher){
                    try {
                        JSObject next = null;
                        try {
                            next  = (JSObject)queue.call("shift", null);
                        } catch (Exception ex){
                            break;
                        }
                        
                        if ( next == null ) break;
                        
                        String pdfURL = (String)next.getMember("url");
                        if ( pdfURL == null ) break;
                        
                        
                        PDFRenderer renderer = new PDFRenderer();
                        URL baseURL = PDFRendererApplet.this.getDocumentBase();
                        String baseURLStr = baseURL.toString();
                        int queryPos = baseURLStr.indexOf("?");
                        if ( queryPos >= 0 ){
                            baseURLStr = baseURLStr.substring(0, queryPos);
                        }
                        
                        if ( !baseURLStr.endsWith("/") ){
                            int lastSlashPos = baseURLStr.lastIndexOf("/");
                            baseURLStr = baseURLStr.substring(0, lastSlashPos+1);
                        }
                        if ( pdfURL.indexOf(":") < 0 ){
                            if ( pdfURL.indexOf("/") == 0 ){
                                pdfURL = baseURL.getProtocol()+"://"+baseURL.getHost()+
                                        (baseURL.getPort()>0?(":"+baseURL.getPort()):"")+pdfURL;
                            } else {
                                pdfURL = baseURLStr+pdfURL;
                            }
                        }
                        
                        System.out.println("PDF URL is "+pdfURL);
                        renderer.setPDFURL(new URL(pdfURL));
                        
                        String req = null;
                        try {
                            req =  (String)next.getMember("request");
                        } catch ( Exception ex){
                            
                        }
                        
                        if ( "numPages".equals(req) ){
                            next.call("update", new Object[]{renderer.getNumPages()});
                        } else {

                            int page = ((Number)next.getMember("page")).intValue();
                            int width = ((Number)next.getMember("width")).intValue();


                            renderer.setWidth(width);
                            renderer.setPage(page);
                            BufferedImage img = renderer.getResult();
                            ByteArrayOutputStream os = new ByteArrayOutputStream();
                            ImageIO.write(img, "png", os);
                            os.flush();

                            String encodedImage = new Base64().encodeToString(os.toByteArray());
                            os.close();

                            next.call("update", new Object[]{encodedImage});
                            
                        }
                        //System.out.println(encodedImage);
                        
                        
                        
                        
                    } catch (Exception ex) {
                        //Logger.getLogger(PDFRendererApplet.class.getName()).log(Level.SEVERE, null, ex);
                        ex.printStackTrace(System.out);
                    } 
                    
                    
                    
                    
                    
                }
                running = false;
            }
            
        });
        dispatcher.start();

See full applet source code

Edit: In newer versions of Java there are additional security restrictions on network requests when the method call is initiated from Javascript. Therefore is is necessary to wrap the startDispatch() method in an AccessController.doPrivileged() call (Solution found here). To accommodate this I renamed the startDispatch() method as int_startDispatch() and I created a new startDispatch() method as follows:

public synchronized void startDispatch(){
        AccessController.doPrivileged(
                
                new PrivilegedAction(){
                    public String run(){
                        int_startDispatch();
                        return "";
                    }
                }
            
            
        );
        
    }

This ensures that we won’t run into security issues when loading the PDFs.

Basically this spawns a thread the runs a loop. In each iteration, a message is loaded from the queue. It gets the PDF’s URL, and other information about what is being requested. When it is done, it calls the update() method of the original message which is a javascript method. It this method is responsible for adding the image back to the DOM.

See a demo of the Web PDF Renderer

Exploring the HTML/CSS Mobile Toolkit Landscape

I’m in the process of developing a mobile interface to Xataface so I’ve been experimenting with different toolkits to help with the UI. I have worked with jqTouch in the past (to create the Web Lite TV iPhone/iPad interface) and found it mostly good, but a little bit heavy, and also buggy when you get into the fine details. (It may have improved since then).

Apple’s Dashcode is also quite impressive for developing UIs in a more WYSIWYG fashion, but I felt a little trapped inside their box and unable to customize things in the way I wanted to, especially when developing apps that needs to generate the UI dynamically – not just populating data to a static layout.

So, going into this, I wasn’t entirely happy with my toolkit. Let the exploration begin for better mobile HTML/CSS toolkits:

iScroll

First stop: iScroll. iScroll, in my opinion, is a major breakthrough in HTML/CSS application development on mobile touch devices. It is the first library to allow good scrolling of the content of any HTML tag. Outside of iScroll, you couldn’t just scroll the contents of a div whose CSS declares that it should be scrollable… even iframes wouldn’t scroll.

Major step forward… but not a complete toolkit. It doesn’t provide any of the familiar nav bars and buttons that users expect in a native app… that is simply beyond the scope of the iScroll project. So we’ll use this as a tie-down point in our expedition, and we’ll proceed to explore toolkits that incorporate iScroll.

Stop 1: appML

appML uses its own custom markup language for web applications, called appML. It is built on jqTouch and iScroll and serves as a framework for building native looking mobile apps (native to iPhone at least). The demos are quite stunning so I decided to give it a go.

First of all, I have to say that the developers are doing a fantastic job on this framework. The UI is very appealing and the markup they have developed is quite intuitive. However this project is still in alpha phase and it has the feel of a project that is still taking shape and forming direction. I like the fact that it is built on jQuery, but it is still a little bulky, and the API is still a little rough around the edges. The show stopper for me was that it isn’t currently possible to load pages using AJAX. This severely limits the types of applications that can be produced. I’m sure that this will be addressed in future versions, but for now I’m forced to move on.

Stop 2: ChocolateChip-UI

ChocolateChip-UI (CHUI) is a real gem of a toolkit. The project has a small, well-defined scope that makes for a clean and polished API. It is well documented, slim, and easy to extend. It defines its own markup language (WAML) similar to the way that appML does, but it is much better documented, and cleanly implemented. It seems to closely mirror the official iOS cocoa API, providing simple tags for all of the major views, containers, and buttons. It is self contained (doesn’t use jQuery or any other bulky libraries) which is nice (I generally still use jQuery in my apps anyways, but it’s nice to have the option of leaving it out).

It is hard to express just how good this toolkit is in relation to all others that I have tried so far. If we were to give any of the other toolkits a 10 out of 10, then Chocolate Chip UI deserves a 50 out of 10.

Application Versioning & Synchronization with Xataface

One of the more annoying challenges involved with managing production web applications is keeping the development and production versions in sync. Verson control systems such as Subversion and CVS make this task trivial for source code and file system changes, but changes to the database schema between versions still need to be handled with care, as these changes fall outside the watch of any version control system.

For example, suppose I am running an application that stores user profile information, and I want to add a column to the “users” table to store the user’s postal code. I add the column to my development database but I don’t want to add it to the production database until I am finished with the rest of my changes.

The old way: Copy & Paste – text files

The old way managing these changes was to make the change in the development database, then copy and paste the SQL query that I used to perform the update into a text file. I would repeat this process for each change that I made. When it came time to move the changes to the production application, I would just execute these statements manually one by one on the production server.

The down-side of this approach is that it didn’t scale very well. It works OK if I only have one production installation and one development server. But what if I have dozens of production servers all running the same application, and perhaps running different versions. It would become cumbersome if not impossible to keep track of all of these changes and manually apply them across all installations.

The new way: Xataface Application Versioning

Xataface allows you to track the version of your application with a text file named version.txt stored in your application’s directory. This file should contain one line like with two numbers separated by a space:

1.0b1 345

This example means that the application version is 1.0b1, and that the build version is 345. The build version must be an integer that is incremented every time there is a change to the source code. It is used by Xataface to figure out whether the file system version matches the database version. A good practice is to just use the SVN revision number for the build version.

On every page request, Xataface checks the version.txt file to see what version of application is currently in the file system. It compares this with the version of the database. If the database version is lower, it will execute the necessary queries to update the database to the current version.

The conf/Installer.php file

Xataface looks for a class named conf_Installer located in your application’s conf/Installer.php file to find out what it needs to do to update between versions. You can define methods in this class of the form:


function update_##(){}

Where ## is the build number of the update.

Xataface will execute all functions update_XX() to update_YY() in your conf_Installer class automatically if it finds that the database version is XX and the filesystem version is YY. This is where you can place your database updates that need to be performed between versions.

For example, suppose the production server is running build version 345. That means that the version.txt file in your production server might look something like:

0.5.1 345

Now you want to add a postal_code column to the users table in the development version, so you’ll increment the version number on the development server:


0.5.2 346

And add a method to your conf/Installer.php file to perform the database change:

<?php
class conf_Installer {
  function update_346(){
    $sql[] = 'ALTER TABLE `users` ADD `postal_code` VARCHAR(32) AFTER `phone_number`';
    foreach ($sql as $q){
      mysql_query($q, df_db());
    }
  }
}

Then you can just update the source files to the production server using subversion. The first time you run the production app after updating the source files you’ll get a message saying that the application has been updated to version 346.

That’s all it takes. You just keep on adding these methods for each update. Then even if you have an instance that is a couple of versions behind, all you need to do is update to the latest source revisions, and it will automatically update the database to the correct version.

Replacing Scriptaculous/Prototype with jQuery

I have used Scriptaculous in the past to sprinkle little bits of UI magic into Xataface. Specifically, I have used it to add collapsible sections, sortable sections (via drag-and-drop), and sortable tables (also via drag and drop). These worked great! The Scriptaculous library was a bit bulky and it made the initial page load time a little bit longer, but the result was worth it.

Unfortunately I have started to run into problems with Scriptaculous interfering with other scripts on the page. Scriptaculous is built on the Prototype.js library which adds a number of handy methods and attributes to the built-in javascript types, like objects, arrays, DOM Elements, and strings. As a proof of concept, this is great as it shows off the dynamic features of the javascript programming language. However this can cause problems with scripts that count on the results of the default behavior of these built-in types.

For example, I have made use of Kevin van Zonneveld’s php.js library which provides pure javascript implementations of familiar PHP functions. One such function is count() which is supposed to return the number of elements in a PHP array. In Javascript, this function can either take objects or arrays as a parameter in order to provide the closest possible behavior to its PHP counterpart. Essentially, all this function does is count the number of elements in the array (or object) and return the result as an integer. Unfortunately, after including the Prototype.js library, all objects now have a number of default properties and methods whether you want them or not because they are added to Object.prototype. This effectively breaks the count() function and I can’t see a viable way to work around the problem other than removing Prototype.js from the mix.

Why does prototype.js break the count() function?

Take the following example:

var o = {0 : 'a', 1: 'b', 2: 'c'};
count(o); // should return 3 but with Prototype.js installed it returns 25

This returns the wrong result because Prototype.js adds a number of methods and properties to all objects in the system, so the count() function must count these also.

jQuery to the Rescue

Luckily there is another library that does everything that I have been using Scriptaculous/Prototype.js for: jQuery. It is leaner and less intrusive. It doesn’t change any of the underlying types and it still provides the drag-and-drop sorting of sections, and collapsing/expanding of sections. And in most cases it provided a cleaner, faster solution than was required with Scriptaculous.

Automatic Website Translation Service

I have just revamped my translation website, shifting the focus to providing machine translations of web sites. The site will allow users to upload their websites as a ZIP file, and have them automatically converted into nearly 30 languages.

I have leveraged quite a bit of open source software to make this happen, including Xataface, and this is only phase one. Xataface already allows developers to easily convert monolingual web applications written in PHP into multilingual applications complete with Google Translate integration, and support for human translation also. I am currently working on some modules to improve interoperability with other translation tools using the XLIFF document standard.

For those people who require a high quality human translation, I have provided a quote form from Translated.Net to get an instant quote. Ultimately translate.weblite.ca will become a portal with all kinds of tools and information about website internationalization. One step at a time ….

Internet Media Manager

I was tired of having to resize my images before uploading them to the web. I also wanted to be able to host more video on my website in a simple way. So I created an application to manage and serve all of my videos and images – Internet Media Manager.

Now I manage all of my media from a central location, and I can easily embed images and video into any of my web pages by copying and pasting a snippet of code.

Here is a brief guided tour: (this video is hosted using the Internet Video Manager):

Some notable features that are included:

  • Amazon Simple Storage Service (S3) integration for hosting video files. I want to host video but I don’t want it bogging down my server. Since Amazon S3 is so cheap and has such amazing capacity, it makes sense to store all of my video files on Amazon. The Internet Video Manager can be set up to automatically move all video files to my S3 account so that I can host video without worry.
  • Embeddable links – Images, videos, and photo galleries in the system can be embedded into any web page by copying and pasting snippets of HTML code.
  • Image resizing and processing – You can select the image size for embedding and the server will automatically resize the image.

PHP/MySQL Time zone design pattern

Suppose you have a PHP/MySQL application that you want to adapt to work across multiple timezones. I.e. Users from Toronto should see all dates and times in Eastern time while Vancouver users should see times in Pacific time. On this surface this appears like a simple thing to do, but there are a number of pitfalls to watch out for.

For this design pattern we’ll assume that we can always obtain the user’s timezone by calling a function, say getUserTimezone() which would return something like Canada/Pacific.

The tricky part is making sure that different users can view and edit dates in their local timezone, while keeping the data consistent. For example, if Toronto Tony posts an event in our application, he can specify that it starts at 8pm (meaning 8pm Toronto time). When Vancouver Vinnie logs in, he sees this event, but the time says ‘5pm’ because he sees the event in Vancouver time.

Design Decisions

  1. How do we store the dates in the database? Do we store them in Toronto time? Vancouver time? Some other time? or does it matter?
  2. Both MySQL and PHP contain functions to convert dates between different timezones. Do we do the conversion in MySQL? In PHP? In both? Does it matter?

Our solution for the first design decision, is that it doesn’t matter how we store the dates in the database, as long as we are consistent. I.e. we must store ALL of the dates in Toronto time or ALL of the dates in Vancouver time, but we cannot store some in one timezone and some in another. When deciding on an appropriate default timezone for the database, I considered using GMT (Greenwich mean time) but it is probably better just to leave the server running the most intuitive time for the majority of users. If the server is physically located in Denver, why not just leave the times in Denver time. We will convert the times to and from the users’ timezones when we insert and retrieve the data from the database.

For the 2nd design decision,, it will be much easier to use the MySQL conversion functions than to use the PHP functions. This will allow us to simplify our handling of the timezone issue exclusively to our SQL queries and leave our PHP code largely alone.

Setting the Timezone in PHP

Inside our PHP script we can set the timezone for the current user by setting the TZ environment variable:

putenv('TZ='.getUserTimezone());

Once we have set the timezone, the PHP date function will properly convert timestamps to the user’s local timezone, and the strtotime function will propertly convert dates in the user’s local timezone back to timestamps.

Now we have 3 choices in how to store our dates in the database and interact with them.

Strategy 1: Store dates as timestamps

Since timestamps are not affected by timezones, we could just store all of our dates in INT columns as timestamps and convert them to dates using PHP. e.g.:

// inserting dates
$date = '2007-12-29 08:00:00';
mysql_query("insert into events ( ..., `startDate`, ...) values ( ..., '".strtotime($date)."', ...)");

// retrieving dates
$res = mysql_query("select `startDate` from events where ...");
list($date) = mysql_fetch_row($res);
$date = date('Y-m-d H:i:s', $date);  // convert the timestamp back to a date string

The main drawback to this strategy is style. Databases are equipped with DATE data types for a reason. By using INT datatypes to store our dates, we are missing out on some nice features for data manipulation etc… Plus it just doesn’t *feel* quite right to be using INT columns when we should be using DATETIME columns.

Strategy 2: Use FROM_UNIXTIME() and UNIX_TIMESTAMP() mysql functions

MySQL provides a some nice conversion functions that will allow us to work with timestamps yet still store our dates in proper DATE and DATETIME columns in the database. The FROM_UNIXTIME() function will convert a timestamp into a date string, and the UNIX_TIMESTAMP() function will convert a date string into a unix timestamp. So we can achieve the same as in Strategy 1 with the following:

// inserting dates
$date = '2007-12-29 08:00:00';
mysql_query("insert into events ( ..., `startDate`, ...) values ( ..., FROM_UNIXTIME('".strtotime($date)."'), ...)");

// retrieving dates
$res = mysql_query("select UNIX_TIMESTAMP(`startDate`) from events where ...");
list($date) = mysql_fetch_row($res);
$date = date('Y-m-d H:i:s', $date);  // convert the timestamp back to a date string

This strategy is a little bit better, in my opinion, because it uses the proper data types for the proper data. However it still requires that we use timestamps as an intermediary. In certain cases you may be unable to use timestamps (either because the rest of the system expects the SQL queries to return and accept date strings directly (not timestamps)), or because timestamps won’t handle the extreme dates in your application (e.g. timestamps are only valid for dates later than 1901 on most systems, and later that 1970 on some).

Strategy 3: Use the MySQL CONVERT_TZ() function

MySQL’s CONVERT_TZ() function will convert a date between two different timezones without requiring the intermediate step of converting to a timestamp. Theoretically, it should be able to accept input of the form:

CONVERT_TZ(`startDate`, 'Canada/Pacific', 'Canada/Eastern');

However if your server doesn’t have the timezone tables installed, then it won’t work correctly with these forms of timezones. The safest way seems to be to specify timezones in the form ‘+08:00’, meaning (8 hours behind GMT). e.g.

CONVERT_TZ(`startDate`, '+01:00', '-08:00');


This creates a small hurdle for us: How do we convert the user’s timezone (in the form ‘Canada/Pacific’) to an offset timezone like ‘+08:00’?

Here is a function that does just that:

/**
 * Returns the current timezone as an offset (e.g. +08:00) of GMT
 **/
utc_offset(){
        // Find the difference in seconds between GMT and local time.
        $diff = gmmktime(0,0,0,12,29,2007) - mktime(0,0,0,12,29,2007);
    $sign = ( ( $diff >= 0 ) ? '+' : '-');
    $diff = abs($diff);
    $hours = str_pad(strval(floor($diff/3600)), 2, '0',STR_PAD_LEFT);
    $minutes = str_pad(strval(floor($diff/60) % 60), 2, '0',STR_PAD_LEFT);
    
    return $sign.$hours.':'.$minutes;
    
}

So we can use our utc_offset() function to get the user’s timezone as an offset from GMT. We can pass this as one of the parameters for CONVERT_TZ(). The other timezone will be the system timezone, which we can conveniently specify as ‘SYSTEM’. e.g.:

-- Convert from -08:00 to system time
CONVERT_TZ(`startDate`, '-08:00', 'SYSTEM')

-- Convert from system time back to -08:00 time.
CONVERT_TZ(`startDate`, 'SYSTEM','-08:00')

We now have all the tools we need to adapt our examples from strategies 1 and 2:

// inserting dates
$date = '2007-12-29 08:00:00';
mysql_query("insert into events ( ..., `startDate`, ...) values ( ..., CONVERT_TZ('$date','".utc_offset()."','SYSTEM'), ...)");

// retrieving dates
$res = mysql_query("select CONVERT_TZ(`startDate`,'SYSTEM','".utc_offset()."') as `startDate` from events where ...");
list($date) = mysql_fetch_row($res);
echo $date;  // 2007-12-29 08:00:00

I used this strategy to add timezone support to Xataface because I needed a solution that would allow me to change the mysql queries without altering any of the code them uses them. It seems to work quite well.

Happy hunting!