Transform time in second to HH:MM:SS

Transform time in second to HH:MM:SS

Posted by: Fabrice.Mainguene on 5 November 2025, 10:26 am EST

• 

  Fabrice.Mainguene

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 5 November 2025, 10:26 am EST

  Hi,

  One of my database columns contains time in seconds. I want to display it in my sheet in HH:MM:SS format using the datasource.

  How can I do that?

  Thanks

• 

  priyam.kushwaha

  

  • Post Options:
  • Link
    Copy

  Posted 6 November 2025, 7:01 am EST

  Hi,

  You can achieve this by creating a custom formatter to convert seconds into HH:MM:SS format and using it with your data source through setDataSource() and bindColumns() after fetching data from your database.

  Below is an example where the timeInSeconds column from the data source is formatted using a custom formatter:

  var spreadNS = GC.Spread.Sheets;
  
  function CustomTimeFormatter() {}
  CustomTimeFormatter.prototype = new GC.Spread.Formatter.FormatterBase();
  
  // Convert seconds → HH:MM:SS
  CustomTimeFormatter.prototype.format = function (obj, formattedData) {
  	let seconds = Number(obj);
  	if (isNaN(seconds) || seconds < 0) return obj != null ? obj.toString() : "";
  
  	let hrs = Math.floor(seconds / 3600);
  	let mins = Math.floor((seconds % 3600) / 60);
  	let secs = Math.floor(seconds % 60);
  
  	if (formattedData) formattedData.conditionalForeColor = "blue";
  
  	return (
  		hrs.toString().padStart(2, "0") +
  		":" +
  		mins.toString().padStart(2, "0") +
  		":" +
  		secs.toString().padStart(2, "0")
  	);
  };
  
  // Convert HH:MM:SS → seconds (optional)
  CustomTimeFormatter.prototype.parse = function (str) {
  	return new GC.Spread.Formatter.GeneralFormatter().parse(str);
  };
  
  window.onload = function () {
  	var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });
  	initSpread(spread);
  };
  
  function initSpread(spread) {
  	var sheet = spread.getSheet(0);
  	sheet.name("Time Formatter (DataSource)");
  	spread.suspendPaint();
  
  	//  Sample data (simulating database records)
  	var data = [
  		{ id: 1, task: "Process A", timeInSeconds: 3661 },
  		{ id: 2, task: "Process B", timeInSeconds: 145 },
  		{ id: 3, task: "Process C", timeInSeconds: 86399 },
  		{ id: 4, task: "Process D", timeInSeconds: 59 },
  		{ id: 5, task: "Process E", timeInSeconds: 7325 },
  	];
  
  	// Set data source
  	sheet.setDataSource(data);
  
  	// Define columns with bindings
  	sheet.autoGenerateColumns = false;
  	sheet.bindColumns([
  		{ name: "id", displayName: "ID" },
  		{ name: "task", displayName: "Task" },
  		{
  			name: "timeInSeconds",
  			displayName: "Duration (HH:MM:SS)",
  			formatter: new CustomTimeFormatter(),
  		},
  	]);
  
  	sheet.setColumnWidth(0, 60);
  	sheet.setColumnWidth(1, 150);
  	sheet.setColumnWidth(2, 160);
  
  	spread.resumePaint();
  }

  This will automatically display time values in HH:MM:SS format on the sheet while keeping the underlying data in seconds. Refer to the attached sample: Sample.zip

  You can also refer to this demo for more details on using a custom formatter: https://developer.mescius.com/spreadjs/demos/features/cells/formatter/custom-formatter/purejs

  Regards,
  
  Priyam