Sunday, August 14, 2011

YUI Date Formatting for MySQL date field

For example we want show report with date column. For this I have used YUI datatable (The DataTable control provides a simple yet powerful API to display screen-reader accessible tabular data on a web page. Notable features include sortable columns, pagination, scrolling, row selection, resizeable columns, and inline cell editing. For more information about YUI datatable read from here). Date field is timestamp type in MySQL DB.

function customerReport(json){
 var formatDate = function(elCell, oRecord, oColumn, sData) {
            elCell.innerHTML = YAHOO.util.Date.format(new Date(YAHOO.util.DataSource.Parser['mysql_date'](sData)), {format: "%d-%b-%G"}, "en-US");
 var myColumnDefs = [
            {key:"number", label:"NO"},
            {key:"date", label:"Date", sortable:true, formatter:formatDate},
            {key:"username", label:"User Name", sortable:true},
            {key:"country", label:"Country", sortable:true},
            {key:"full_name", label:"Full Name", sortable:true},
            {key:"phone", label:"Phone"},
            {key:"email", label:"E-mail Address"}

 var myDataSource = new YAHOO.util.DataSource(json);
        myDataSource.responseType = YAHOO.util.DataSource.TYPE_JSON;
        myDataSource.connXhrMode = "queueRequests";
        myDataSource.responseSchema = {
            resultsList: "data",
            fields: ["number", "date", "username", "country", "full_name", "phone", "email"]
 var myConfigs = {
            paginator: new YAHOO.widget.Paginator(
    rowsPerPage: 10, 
    containers: "report-paginator",
    firstPageLinkLabel : "<< ", 
    previousPageLinkLabel : "< previous", 
    nextPageLinkLabel : "next >", 
    lastPageLinkLabel : " >>"
            MSG_EMPTY: "No result",
            MSG_LOADING: "loading"
 var myDataTable = new YAHOO.widget.DataTable("customer-report", myColumnDefs, myDataSource, myConfigs);

And date format function for our example shown bellow:
YAHOO.util.DataSource.Parser['mysql_date'] = function (oData) {
    var parts = oData.split(' ');
    var datePart = parts[0].split('-');     // assumes in YYYY-MM-DD format
    if (parts.length > 1) {
       var timePart = parts[1].split(':');
       return new Date(datePart[0],datePart[1]-1,datePart[2],timePart[0],timePart[1],timePart[2]);
    } else {
       return new Date(datePart[0],datePart[1]-1,datePart[2]);

Now, u can show your date column in different format. For more information about YUI date format read from here. And sort for date column works fine independent which format u accept for this column.

That's all u need for this problem :))


  1. where is connection file and query ? can you send fulll code please

  2. Connection is standard JDBC connection. I get query result and generate JSON object sent it back.

  3. Hi.

    that is the part I need to know. Could you please add to your post?.