PocketQuery how do i format an Annotation Chart

[dean bateman]

updated_at	CAST(SUBSTRING(carts.items,(LOCATE(374, carts.items)+5),1) AS UNSIGNED)
1412127728000	1
1412128259000	3
1412127728000	2

i have a table containing datetime and a quantity columns

Id like to use an annotation chart but i am not sure of the naming convention you are using for the chart types

I have tried this code but i do not get any joy, where am i going wrong?

<script>
PocketQuery.chart('annotationchart', {displayAnnotations: true});
</script>

&&

<script>
PocketQuery.chart('annotationchart', {
title: 'Part Orders',
allowHtml: true,
showRowNumber: true,
format: {
type: 'updated_at',
column: 1
}
});
</script>

Ive checked so code, and do you only support these types?

["Table","TreeMap","GeoChart","Gauge","Map"]

[Felix Grund (Scandio)]

Hi Dean,

Finally I'm coming back to you about this. I just created PocketQuery 1.14.3 which you can find at the marketplace now.

With this version, you can do a few manipulations to the data produced for the chart with JavaScript. Most probably your problem is solved as soon as you convert your dates to JavaScript dates. For me it worked with this example template:

<script>
(function() {
	// Create a new data table object in which the first row already contains
	// the table headers. Assume the first column contains a date that was
	// provided to PocketQuery as a timestamp number.
	var dataTable = [['Date', 'Number Column', 'String Column']];
	
	// Iterate through the PocketQuery result and convert every timestamp to
	// a JS date object. Add a row to the dataTable for each row in the result.
	jQuery.each(PocketQuery.queryArray(), function(index, row) {
		dataTable.push([new Date(row.updated_at), row.number, row.title]);
	}); 

	PocketQuery.chart('AnnotationChart', {dataTable: dataTable});
}());
</script>

You can dig in this functionality with a few console.log(...) or a few debugger statements. Let me know if it helps!

Regards, Felix [Scandio]

[Felix Grund (Scandio)]

Hi Dean!

Thanks for this information. Please first note that your template doesn't make sense: your object with the "cols" array is not an object to be passed to the PocketQuery chart function. It's just the JSON object created automatically as chart data. So in your case don't pass anything, just call

<script>
PocketQuery.chart('AnnotationChart');
</script>

The problem now is that PocketQuery doesn't recognise your Date field as a date. It's quite hard to analyse this issue here. I can offer you that you send me a SQL backup of your relevant database tables and I will see what I can do to create an AnnotationChart out of it. You can of course blacken the data if it's sensitive. I'm really interested in this issue myself now and I'd be happy to proceed like that.

Regards, Felix [Scandio]

[dean bateman]

here is a sample of the data - http://1drv.ms/1uSEY75 (i have only exported the two relevant columns) This is the query i run to produce the data =========================================================================== SELECT SUBSTRING(carts.updated_at,1,10) AS Date, CAST(SUBSTRING(carts.items,(LOCATE(:id, carts.items)+5),1) AS UNSIGNED) AS Qty FROM carts WHERE items LIKE CONCAT('%', :id, '%'); =========================================================================== you will need to choose an id of say 664 within your macro on the page to display the chart. =========================================================================== Would it be possible to run on DateTime? here is the data format from google Data Format You can display one or more lines on your chart. Each row represents an X position on the chart—that is, a specific time; each line is described by a set of one to three columns. The first column is of type date or datetime, and specifies the X value of the point on the chart. If this column is of type date (and not datetime) then the smallest time resolution on the X axis will be one day. Each data line is then described by a set of one to three additional columns as described here: Y value - [Required, Number] The first column in each set describes the value of the line at the corresponding time from the first column. The column label is displayed on the chart as the title of that line. Annotation title - [Optional, String] If a string column follows the value column, and the displayAnnotations option is true, this column holds a short title describing this point. For instance, if this line represents temperature in Brazil, and this point is a very high number, the title could be "Hottest day on record". Annotation text - [Optional string] If a second string column exists for this series, the cell value will be used as additional descriptive text for this point. You must set the option displayAnnotations to true to use this column. You can use HTML tags, if you set allowHtml to true; there is essentially no size limit, but note that excessively long entries might overflow the display section. You are not required to have this column even if you have an annotation title column for this point. The column label is not used by the chart. For example, if this were the hottest day on record point, you might say something like "Next closest day was 10 degrees cooler!".

[Felix Grund (Scandio)]

Thanks! Can you also provide your "CREATE TABLE" statement? On what DBMS are you on? MySQL?

[dean bateman]

I'm using MySQL, I believe this statement should create the table, you may or may not need the id column, CREATE TABLE `carts` ( `id` int(11) NOT NULL AUTO_INCREMENT, `items` varchar(1024) NOT NULL DEFAULT '[]', `updated_at` datetime NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=401760 DEFAULT CHARSET=utf8

[Felix Grund (Scandio)]

Hi Dean!

Sorry again for the late reply, somehow I didn't get a notification on this. So important is this:

{
   "cols":[
      {
         "id":"DateTime",
         "label":"DateTime",
         "type":"date"
      },
      {
         "id":"Qty",
         "label":"Qty",
         "type":"number"
      }
   ]
}

If the provided values are correct timestamps AND the type is "date", then it should work (I think so at least). So currently, I'm setting the types in the result JSON string server-side, according to the Java type of the object. This type is mapped by the JDBC driver.

In your case, the Google Charts API complains about 1412127728000 not being a date. Which is kind of correct, since if I convert it to a time string, it resolves to "Fri, 19 Jul 46718 22:13:20 GMT". So the year part is definitely wrong.

What are the actual values in your database for this?

Regards, Felix [Scandio]

[dean bateman]

hi felix, i havent fully tested this yet but 1412127728000 needs to be divided by 1000 = 1412127728 = Wed, 01 Oct 2014 01:42:08 GMT http://www.onlineconversion.com/unix_time.htm i will check the raw data, and post some samples

[dean bateman]

So ive done some quick test and changed the SQL statement to use only the first 10 digits instead of 13 and then using 16 i now have dates but still have this error First column must contain date, or date and time.× here is my template ===================================================================== <script> PocketQuery.chart('AnnotationChart', { "cols":[ { "id":"Date", "label":"Date", "type":"date" }, { "id":"Qty", "label":"Qty", "type":"number" } ] } ); </script> ===================================================================== SELECT SUBSTRING(carts.updated_at,1,10) AS Date, PocketQuery.chartJson("Part-orders-simple", "{\"cols\":[{\"id\":\"Date\",\"label\":\"Date\",\"type\":\"string\"},{\"id\":\"Qty\",\"label\":\"Qty\",\"type\":\"number\"}],\"rows\":[{\"c\":[{\"v\":\"2014-09-30\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":3}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2013-07-03\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-02-04\"},{\"v\":1}]}]}"); PocketQuery.queryJson("Part-orders-simple", "[{\"Date\":\"2014-09-30\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":3},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2013-07-03\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-10-01\",\"Qty\":1},{\"Date\":\"2014-02-04\",\"Qty\":1}]"); PocketQuery.queryColumns("Part-orders-simple", "[\"Date\",\"Qty\"]"); ===================================================================== SELECT SUBSTRING(carts.updated_at,1,16) AS Date, PocketQuery.chartJson("Part-orders-simple", "{\"cols\":[{\"id\":\"Date\",\"label\":\"Date\",\"type\":\"string\"},{\"id\":\"Qty\",\"label\":\"Qty\",\"type\":\"number\"}],\"rows\":[{\"c\":[{\"v\":\"2014-09-30 17:22\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 15:07\"},{\"v\":3}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2013-07-03 05:22\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:45\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:50\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-10-01 11:42\"},{\"v\":1}]},{\"c\":[{\"v\":\"2014-02-04 09:36\"},{\"v\":1}]}]}"); PocketQuery.queryJson("Part-orders-simple", "[{\"Date\":\"2014-09-30 17:22\",\"Qty\":1},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2014-10-01 15:07\",\"Qty\":3},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2013-07-03 05:22\",\"Qty\":1},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2014-10-01 11:45\",\"Qty\":1},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2014-10-01 11:50\",\"Qty\":1},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2014-10-01 11:42\",\"Qty\":1},{\"Date\":\"2014-02-04 09:36\",\"Qty\":1}]"); PocketQuery.queryColumns("Part-orders-simple", "[\"Date\",\"Qty\"]"); =====================================================================

[dean bateman]

im noticing the date is set to string type \"cols\":[{\"id\":\"Date\",\"label\":\"Date\",\"type\":\"string\" and the results are in commas "2014-10-01 11:42\"

[dean bateman]

Here is a sample of the raw data from my SQL database it shows the carts.updated_at (DateTime) and a list of carts.items (varchar(1024)) which are usually filtered to select the substring by item id, then retrieve the trailing qty number from. '2013-08-19 14:17:05', '{\"502\":1,\"310\":1,\"346\":1}' '2013-08-19 14:17:05', '{\"502\":1,\"310\":1,\"346\":1}' '2013-08-19 14:17:05', '{\"502\":1,\"310\":1,\"346\":1}' '2013-08-19 14:17:05', '{\"502\":1,\"310\":1,\"346\":1}' '2013-08-19 14:17:05', '{\"502\":1,\"310\":1,\"346\":1}' So as mentioned above, maybe there needs to be a division by 1000, or the substring function used, but then the type needs to be Date or DateTime preferably, and not a string type, also if there are any quotation marks they would need to be removed. PocketQuery.chartJson("Part-orders-simple", "{\"cols\":[{\"id\":\"Date\",\"label\":\"Date\",\"type\":\"DateTime\"},{\"id\":\"Qty\",\"label\":\"Qty\",\"type\":\"number\"}],\"rows\":[{\"c\":[{\"v\":\2014-09-30 17:22\},{\"v\":1}]},{\"c\":[{\"v\":\2014-10-01 11:42\},{\"v\":1}]},{\"c\":[{\"v\":\2014-10-01 15:07\},{\"v\":3}]},

[Felix Grund (Scandio)]

Hi Dean!

I think the problem is when the JSON string for the Google Charts API is generated on the server. We are dynamically "guessing" the column types for GC and there was no type date yet. I have a snapshot version in which I implemented a fix for this. Could you install this version on your test system and see if it works?

Regards, Felix [Scandio]

[dean bateman]

Hello Felix, ON PAGE DISPLAYED Type mismatch. Value 1412127728000 does not match type date× JSON DATA, PocketQuery.chartJson("Part-Orders", "{\"cols\":[{\"id\":\"DateTime\",\"label\":\"DateTime\",\"type\":\"date\"},{\"id\":\"Qty\",\"label\":\"Qty\",\"type\":\"number\"}],\"rows\":[{\"c\":[{\"v\":1412127728000},{\"v\":\"1\"}]},{\"c\":[{\"v\":1412128259000},{\"v\":\"1\"}]},{\"c\":[{\"v\":1412127728000},{\"v\":\"1\"}]}]}"); PocketQuery.queryJson("Part-Orders", "[{\"DateTime\":1412127728000,\"Qty\":\"1\"},{\"DateTime\":1412128259000,\"Qty\":\"1\"},{\"DateTime\":1412127728000,\"Qty\":\"1\"}]"); PocketQuery.queryColumns("Part-Orders", "[\"DateTime\",\"Qty\"]"); It probably quite important to say, that the format is "DateTime" and not "Date" as the annotation chart uses the time information too I tried converting the SQL data to show date only "CAST(carts.updated_at AS DATE)" JSON DATA2, PocketQuery.chartJson("Part-Orders-2", "{\"cols\":[{\"id\":\"date_ordered\",\"label\":\"date_ordered\",\"type\":\"date\"},{\"id\":\"Qty\",\"label\":\"Qty\",\"type\":\"number\"}],\"rows\":[{\"c\":[{\"v\":1412085600000},{\"v\":\"1\"}]},{\"c\":[{\"v\":1412085600000},{\"v\":\"1\"}]},{\"c\":[{\"v\":1412085600000},{\"v\":\"1\"}]}]}"); PocketQuery.queryJson("Part-Orders-2", "[{\"date_ordered\":1412085600000,\"Qty\":\"1\"},{\"date_ordered\":1412085600000,\"Qty\":\"1\"},{\"date_ordered\":1412085600000,\"Qty\":\"1\"}]"); PocketQuery.queryColumns("Part-Orders-2", "[\"date_ordered\",\"Qty\"]");

[Felix Grund (Scandio)]

Hi Dean!

I think the problem is how the JSON serializer processes your date values. It seems it converts it to the timestamp numbers visible in your sample JSON. Maybe you could try to convert your date results to strings in your SQL statement? It's unfortunately not possible currently to manipulate the JSON before it's given to the Google Charts API library. So you must make sure the data is already as you desire when it arrives at the client.

Can you show me your full SQL query?

Regards, Felix [Scandio]

[dean bateman]

I have a procedure setup that uses the page name which is also the part number (@page) which is parsed to :number to display the content within a template. SQL Query CALL GetPartHistory (:number,@PartId); SQL Procedure CREATE DEFINER=`user`@`%` PROCEDURE `GetPartHistory`( IN PartNo VARCHAR(255), OUT PartId INT) BEGIN set @id_variable = (SELECT id FROM items WHERE number = PartNo LIMIT 1); SELECT carts.updated_at AS DateTime, SUBSTRING(carts.items,(LOCATE(@id_variable, carts.items)+5),1) AS Qty FROM items, carts WHERE items.number = PartNo AND items LIKE CONCAT('%', @id_variable, '%') AND (carts.status='closed' OR carts.status='shipped'); END It would be "carts.updated_at AS DateTime" i would need to edit to display the date and time the column it is set to DateTime, which is what i will need.

[Felix Grund (Scandio)]

Hi Dean!

I think a similar issue was raised before here: https://answers.atlassian.com/questions/292968

Could you check if it is related to your problem?

Regards, Felix [Scandio]

[dean bateman]

Thanks i'll check it out and report back

[dean bateman]

Here are my JSON row data for updated_at(DATETIME) and quantity(NUMBER) just not sure how to show this in date form \"rows\": [{\"c\":[{\"v\":1412127728000},{\"v\":1}]}, {\"c\":[{\"v\":1412128259000},{\"v\":1}]}, {\"c\":[{\"v\":1390176558000},{\"v\":1}]}, {\"c\":[{\"v\":1412128259000},{\"v\":1}]}, {\"c\":[{\"v\":1412127846000},{\"v\":1}]}]}"); I've tried alsorts it seems to be linked to googles api but if this it may help https://github.com/mbostock/d3/wiki/Time-Formatting http://stackoverflow.com/questions/6158223/google-charts-api-datetime-unix-format https://groups.google.com/forum/#!topic/d3-js/rue-bVT66Q0 https://groups.google.com/forum/#!topic/d3-js/BW2V5A5iHSo

[dean bateman]

here is a fiddle showing code to convert the json http://jsfiddle.net/JamesHill/Kbaap/

[dean bateman]

If i use this code in the template it will return dates, #foreach($row in $result) <p>$row.updated_at,</p> #end 2014-10-01 11:42:08.0, 2014-10-01 11:50:59.0, 2014-01-20 11:09:18.0, 2014-10-01 11:50:59.0, 2014-10-01 11:44:06.0,

[Felix Grund (Scandio)]

I will check later on. Sorry for the delay...

[dean bateman]

Thank you Felix, your help is greatly appreciated :)

[Felix Grund (Scandio)]

Hi Dean,

Thank you for your interest in PocketQuery! You're quite right with the snippet you found in our code. These are the types PocketQuery currently supports that are not in the CoreChart package of Google Charts. This has technical reasons.

I uploaded a snapshot version that includes the AnnotationChart type. Could you test your scenario with that version and tell me if it works? If so, we will include this change in our next release 1.13.4 which I will release very soon.

Regards, Felix [Scandio]

[dean bateman]

I am having an issue with getting the datetime displayed as it should be and not as a string "1412085600000" ive tried using a CAST function to no avail CAST(carts.updated_at AS DATETIME) my issue being this code \"type\":\"string\" so no matter how i change my query it reverts back to a string funnily enough though if i use the default template the table will show "2014-10-01 11:50:59.0" even though the data is still type string Sample of the data output PocketQuery.chartJson("Part-orders-simple", "{\"cols\":[{\"id\":\"CAST(carts.updated_at AS DATE)\",\"label\":\"CAST(carts.updated_at AS DATE)\",\"type\":\"string\"},{\"id\":\"CAST(SUBSTRING(carts.items,(LOCATE(\'654\', carts.items)+5),1) AS UNSIGNED)\",\"label\":\"CAST(SUBSTRING(carts.items,(LOCATE(\'654\', carts.items)+5),1) AS UNSIGNED)\",\"type\":\"number\"}],\"rows\":[{\"c\":[{\"v\":1412085600000},{\"v\":1}]},{\"c\":[{\"v\":1412085600000},{\"v\":1}]},{\"c\":[{\"v\":1390136400000},{\"v\":1}]},{\"c\":[{\"v\":1412085600000},{\"v\":1}]},{\"c\":[{\"v\":1412085600000},{\"v\":1}]}]}");

[dean bateman]

Thanks

[Tim]

make sure you add the addon-de.scandio.confluence.plugins.pocketquer label to pocketquery related questions, as most people, that can help you are monitoring questions, based on that label