Postgres - Query Not working because of extra '::interval" part of query

[Rahul Bahl]

Hi Support Team,

I am converting the time of a column from seconds to a format "hh:mm:ss" with the below query, and now is seems that pocket query is interpreting "::interval" as a parameter. To confirm there is no issue with the query I have tested it directly on how database and it works. Please advise how to correct this (query shown below). Thanks.

Regards,

Rahul

----------

Query:

select "Create Date", "Requet Number" AS "Request Number", "Request Name", "name" AS "Environment",  select "Create Date", "Requet Number" AS "Request Number", "Request Name", "name" AS "Environment",  TO_CHAR((requestruntimeinsec || 'second')::interval, 'HH24:MI:SS') AS "Duration", "aasm_state" AS "Status", "Completed At with Timestamp" AS "Completion Time" from tableau_trends where application_name= :Application_Name;

[Felix Grund (Scandio)]

Hi Rahul,

We are planning to release PocketQuery 2.1 in February. This version won't interpret strings with two colons (e.g. ::interval) at all and should solve your problem. I just prepared an early access version for you here. Could you test if this version solves your issue?

Regards, Felix

[Rahul Bahl]

Hey Felix,

Thanks for the update. I will give it a go either tomorrow or early next week and let you know how it goes. Thanks.

Regards,

Rahul

[Rahul Bahl]

Hi Felix,

Is there some way of simplifying this? There will be other people on the team working on this and if I am unavailable they will be stuck trying to figure it out on their own. Thanks.

Regards,

Rahul

[Felix Grund (Scandio)]

Hi Rahul! Currently I'm afraid there is no easier way. Do you have multiple queries like this that have to be administered?

[Rahul Bahl]

Hi Felix, I can't confirm if the queries will be the exact same but I know that some queries are much more complicated than this one. Hence the need for a simpler way to do what was provided above if there is any possibility. Thanks. Regards, Rahul

[Felix Grund (Scandio)]

If there was a way to escape the colon such that is would not be interpreted as parameter, would that work for you?

[Rahul Bahl]

Hey Felix, That would definitely work :). Thanks. Regards, Rahul

[Felix Grund (Scandio)]

Hi Rahul,

I would try to keep the query simpler and do the logic in a PocketQuery template. I created two Velocity macros that should convert the seconds you get from the database to a date string. I tested those and they work fine. The rest of the template I haven't really tested but it should work somehow like this. Also check the article PocketQuery Templating for general templating documentation.

Query:

SELECT 
	"Create Date",
	"Requet Number",
	"Request Name",
	"name" AS "Environment",
	"requestruntimeinsec" AS "Duration",
	"aasm_state" AS "Status",
	"Completed At with Timestamp" AS "Completion Time"
FROM tableau_trends
WHERE application_name= :Application_Name;

Template:

#macro(zeroPad $myNumber)
#if($myNumber < 10)
#set($myNumber = "0${myNumber}")
#end
$myNumber## dummy comment against trailing whitespace
#end

#macro(createDateString $totalSeconds)
#set($myHours = $totalSeconds / 3600)
#set($myMinutes = ($totalSeconds % 3600) / 60)
#set($mySeconds = $totalSeconds % 60)
#zeroPad(${myHours}):#zeroPad(${myMinutes}):#zeroPad(${mySeconds})
#end

<table class="pocketquery-table confluenceTable">
  <tr>
		<th>Create Date</th>
		<th>Requet Number</th>
		<th>Request Name</th>
		<th>Environment</th>
		<th>myDate</th>
		<th>Status</th>
		<th>Completion Time</th>
  </tr>
  #foreach ($row in $result)
		<tr>$row.get("Create Date")</tr>
		<tr>$row.get("Requet Number")</tr>
		<tr>$row.get("Request Name")</tr>
		<tr>$row.get("Environment")</tr>
		<tr>#createDateString($row.get("Duration"))</tr>
		<tr>$row.get("Status")</tr>
		<tr>$row.get("Completion Time")</tr>
  #end
</table>

Note: you could also use the PQ JavaScript API and do some charting. Something along these lines:

<script>
(function() {
  var result = PocketQuery.queryArray();
  var dataTable = [PocketQuery.queryColumns()];
  jQuery.each(result, function(index, row) {
  	var myDate = new Date(row['Duration']);
  	// ...here you can do whatever with the date and JS...
    dataTable.push([
    	row['Create Date'],
    	row['Requet Number'],
    	row['Request Name'],
    	row['Environment'],
    	myDate,
    	row['Status'],
    	row['Completion Time']
	]);
  });
  PocketQuery.chart('Table', {
    dataTable: dataTable
  });
}())
</script>

Let me know if you need more help!

Regards, Felix (Scandio)

[Rahul Bahl]

Hi Felix,

I’m not sure of how to change the query to not use ‘::interval’. Are there any escape characters I can use in pocket query so that it does not interpret it and still not impact the behavior/results of the query? Thanks.

Regards,

Rahul

[Felix Grund (Scandio)]

Can you provide any resources on what this "::interval" flag is? I can't currently find anything in the Prostgres documentation. I haven't seen it before.

[Rahul Bahl]

Sure Felix, here is an example from the postgres documentation - http://www.postgresql.org/docs/9.4/static/functions-formatting.html From documentation - to_char(double precision, text) text convert real/double precision to string to_char(125.8::real, '999D9') This is where I saw the query used - http://gaganonthenet.com/2013/08/06/postgresql-convert-seconds-to-hhmmss/ Hope this helps. I will be on vacation and not returning until the first week in the new year. Will check back with you once I'm back. Happy Holidays :). Regards, Rahul

[Felix Grund (Scandio)]

Hi Rahul,

Indeed, every word that starts with a colon, will be interpreted as a query parameter, except that it's within single quotes. That's why the colons in 'HH24:MI:SS' won't be interpreted. Is there a way to change your statement such that you don't have to use a colon?

Regards, Felix