9 answers

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:

&lt;script&gt;
(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});
}());
&lt;/script&gt;

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]

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

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.

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}]}]}");

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

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,

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

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.

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.

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

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?

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.

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}]},

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.

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

&lt;script&gt;
PocketQuery.chart('AnnotationChart');
&lt;/script&gt;

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.

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!".

You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.