This is part of an initiative of mine to document some of the challenges in using scientific data with Google Charts. However, this particular write-up just deals with converting MySQL’s datetime to Javascript, since Javascipt deals with recording months differently.

Month Indexing

Why is this even necessary? Well, MySQL stores dates in a familiar manner (YYYY-MM-DD). For example, today, November 6th 2014, would be stored as 2014-11-06. On the other hand, Javascript uses a 0-based indexing rule for the month component of the date. In Javascript, today would be 2014-10-06. This obviously causes problems, particularly when you’re creating a Google Chart that uses a database backend. This technique supports that effort, but be advised that all we’re covering in this post is how to convert datetime to Javascript.

MySQL includes two timestamp data types that explicitly include a numbered month–date (YYYY-MM-DD) and datetime (YYYY-MM-DD hh:mm:ss). As such, I want to make sure my conversion accommodates both data types. Worth mentioning is that although dates and datetimes are rigid data types within the database, once they’ve been queried and manipulated by PHP, they are plain old strings. For this reason, it makes sense to use regular expressions (regex) to extract the individual components of these strings. I should point out this post on StackOverflow that saved me time in figuring out the proper regex.

Datetime to Javascript

Understanding this approach is 9/10 of the battle. Once the month-integer has been extracted from the date-string, a 1 is subtracted from its value and the date string can be built back up into any format. In this case, I rebuild the string to the format expected by a Google Chart.

function JSdate($in,$type){
    if($type=='date'){
        //Dates are patterned 'yyyy-MM-dd'
        preg_match('/(\d{4})-(\d{2})-(\d{2})/', $in, $match);
    } elseif($type=='datetime'){
        //Datetimes are patterned 'yyyy-MM-dd hh:mm:ss'
        preg_match('/(\d{4})-(\d{2})-(\d{2})\s(\d{2}):(\d{2}):(\d{2})/', $in, $match);
    }
    
    $year = (int) $match[1];
    $month = (int) $match[2] - 1; // Month conversion between indexes
    $day = (int) $match[3];
    
    if ($type=='date'){
        return Date($year, $month, $day);
    } elseif ($type=='datetime'){
        $hours = (int) $match[4];
        $minutes = (int) $match[5];
        $seconds = (int) $match[6];
        return Date($year, $month, $day, $hours, $minutes, $seconds);    
    }
}

Using the above function requires two inputs; the MySQL-formatted date/datetime string and whether the string is to be returned as a date, or a datetime.

echo JSdate('2014-11-06','date')

Returns: Date(2014, 10, 06)

echo JSdate('2014-11-06 16:56:20','datetime')

Returns: Date(2014, 10, 06, 16, 56, 20)