PocketQuery: Display Date format for hAxis

[Sreedhar PV]

Hi Team,

I am using PocketQuery macro to display charts for few use cases in confluence. But for one of my use case, I need to display number of spaces created per day for specific period. So my query is as below. 

SELECT 
    DATE(s.creationdate) 'Creation Date',
    COUNT(DISTINCT s.SPACENAME) 'No.Of Spaces'
FROM
    confluence.SPACES s
        JOIN
    confluence.SPACEPERMISSIONS p ON s.SPACEID = p.SPACEID
        JOIN
    confluence.user_mapping u ON p.PERMUSERNAME = u.user_key
WHERE
    p.PERMTYPE = 'SETSPACEPERMISSIONS'
        -- AND s.creationdate >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY DATE(s.creationdate)
ORDER BY s.creationdate DESC;

Query returns data correctly but at the time of rendering the dataset on chart, x-axis displaying the time in milliseconds instead of displaying in Date format. Below is the snapshot for your reference. If you look at x-axis (hAxis), it is displaying in milliseconds. 

PocketQuery.png

I am using below simple script for template. Please let me know how to convert my x-axis into DateFormat.

<script>
PocketQuery.chart('LineChart',
{
  title: 'No.of Spaces per Day'}
);
</script>

Thanks,

Sreedhar

[Felix Grund (Scandio) · Accepted solution]

Hi Sreedhar,

Thank you for your interest in PocketQuery! We've had this problem before. The problem is that the underlying JSON library that passes information from PQ to the Chart API serializes all dates to numbers. But there is a way you can reformat your data in a proper way for the Chart API. However, you will need some JS programming in your template. I did this for you...

<script>
(function() {
	// name of the "creation date" column => must match the name in your statement
	var nameCreationDate = 'Creation Date';
	// name of the "number of spaces" column => must match the name in your statement
	var nameNoOfSpaces = 'No.Of Spaces';
	// the result of your query as JS array
	var result = PocketQuery.queryArray('ContentCreationDate');
	// new 2-dimensional array proper for the chart api
	// first row must be the headers
	var dataTable = [[nameCreationDate, nameNoOfSpaces]];
	// iterate through the result
	jQuery.each(result, function(rowIndex, row) {
		var creationDate = row[nameCreationDate]; // the creation date in the row as number
		var noOfSpaces = row[nameNoOfSpaces]; // the number of spaces in the row
		creationDate = new Date(creationDate); // this is the reformatting number to date
		dataTable.push([creationDate, noOfSpaces]); // add a new array with the two values to the dataTable
	});
	
	
	PocketQuery.chart('LineChart', {
		title: 'No.of Spaces per Day',
		dataTable: dataTable // pass the dataTable to the chart api
	});
}());
</script>

Please note: nameCreationDate and nameNoOfSpaces must match the names of the columns (implicit AS-clause) in your SQL query!

Let me know if this helps!

Regards, Felix (Scandio)

[Sreedhar PV]

Thank you very much for your prompt response. Script is working as expected.