[PocketQuery] Google Chart Api formatting all dates in Epoch...

[Deleted user]

I've tried using hAxis: {format: 'MM/d/y'} to force it to a more normal format, but it doesn't do anything at all...Is there anyway to force the format for a specific column in a pocketquery dataset that's been returned from the query, and then render the chart?

<!-- Template Contents -->

<script>

var options = { hAxis: {format: 'MM/d/y'} };

PocketQuery.chart('LineChart', options);

</script>

That just blatantly ignores the format change even though it should adjust accordingly...

[Felix Grund (Scandio) · Accepted solution]

I analysed this a little further and found the source of the problem: we're serializing the result from the database to JSON using (FlexJSON) and then passing the JSON to Google Charts. FlexJSON serializes all date strings to epoch numbers and in Google Charts this number is displayed.

However, I also checked what would happen if FlexJSON didn't serialize the strings to numbers. If the strings stay in a default date format in the JSON string, Google Charts won't format the dates properly either.

I'm currently implementing a new feature in PocketQuery with which you'll be able to override the data that is given to the Google Charts API. With this feature, you can use a DateFormatter object and make the dates format as you desire. This will take some more time though and will be part of either PocketQuery 1.13 or 1.14. In the meantime, your workaround seems quite smart to me.

[Deleted user]

Thanks for the update. I'm still exploring what's possible with the Google Charts api through your addon so far things are working really well. I'll definitely be looking forward to the update.

[ckoch]

Thanks for the feedback! If you like it, why not leave a little review on the Marketplace? :)

[Felix Grund (Scandio)]

Hi Jason,

Sorry I read that post a little too late. I hope you're still interested in a reply ;). Can you please show me your SQL statement? The rendered table when you use the default template would be helpful too.

Felix

[Deleted user]

I ended up getting it to work by doing the following in the query itself. It would be nice to be able to format with hAxis properties, seems some work, while others do not.

CONVERT(nvarchar(10),dateadd(DAY,0,datediff(day,0,CreatedTime)), 101)

In both tables and charts that I have tried to just use the CreatedTime and then apply hAxis format string, it still renders the CreatedTime in epoch.