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

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;

5 answers

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

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

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.

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

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)

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

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

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

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

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

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

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Jul 10, 2018 in Marketplace Apps

If you’re an Atlassian app developer, you’ll want to know about Atlas Camp!

This September 6-7, hundreds of Atlassian App developers will flock to Barcelona Spain to build skills, discover product roadmaps, meet face-to-face with the Atlassian team, and learn how to extend t...

173 views 0 4
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you