Working with worldwide companies has some pros and cons. One of the cons is work with timezones. When storing info in a database, you really only have the option to store a date/time value in one timezone. Then, the challenge is displaying the date and time to the user. Do you just go through the steps in conveying what timezone the database or server is in and tell them to adjust accordingly? That doesn’t really make for a good user experience.

Here are the steps I’ve taken in the years of working with MySQL, PHP and Javascript in a worldwide market. I haven’t had any complaints so far.

First off, set up your database to be in the UTC +0 timezone. It doesn’t matter where you are located.

Next, when you output the date/time (DATETIME format in MySQL) in HTML, wrap it in a <span> with a special class. I like to use “date-m2l”. For me, that means “date, mysql to local”.

Here are 2 Javascript functions you’ll want to have for this. You can add them globally to your site. They’re lightweight.

function mysql2JsDate(str) {
    var t = str.split(/[- :]/);
    return new Date(t[0], t[1] - 1, t[2], t[3], t[4], t[5]);
}

function mysql2JsLocal(str) {
    var g = mysql2JsDate(str);
    return new Date(g.getTime() - (g.getTimezoneOffset() * 60000));
}

Now, using jQuery, you can add this code snippet and it will convert all of those MySQL formatted date/time instances into the user’s locale time and format. In the below example, I’m using the ‘date-m2l’ class. You can use whatever class you like.

$('.date-m2l').each(function(){
    var mysqlTime = $(this).text();
    var localTime = mysql2JsLocal(mysqlTime).toLocaleString();
    $(this).text(localTime);
});

You’re basically letting the user’s browser interpret the time for you.

Got any other tips for working with timezone across the globe?? Leave me a comment below!