added parameter date/time

[artafon]

Hi, I need to get fast report with date period, for example:

select count(ji.issuenum) as "QTY", os.caller as "USER" from jiraissue ji

join os_historystep os on ji.workflow_id = os.entry_id

where ji.project='13000' and os.finish_date < :min and os.finish_date > :max and os.action_id=5

and os.caller in (select cwdm.lower_child_name from cwd_membership cwdm where parent_id='17511')

group by os.caller

and I want to set date period in min and max paramets.

Any idea to resolve? 

[Felix Grund (Scandio) · Accepted solution]

Hi Artashes,

I checked this with my local MySQL JIRA database and this query works for me:

SELECT ID, FINISH_DATE
FROM os_historystep
WHERE FINISH_DATE > '2016-06-12'

This is the output:

image2016-6-16 15:21:56.png

If I changed the query to this:

SELECT ID, FINISH_DATE
FROM os_historystep
WHERE FINISH_DATE > :min
AND FINISH_DATE < :max

I supply 2015-01-01 as min and 2016-12-31 as max. The output is the same, which means it works perfectly. Am I in a wrong direction here? Or does your DB not support the < > operators for date strings?

Regards, Felix (Scandio)

image2016-6-16 15:21:56.png

[artafon]

Felix, I'm using postgre and have nex error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: timestamp with time zone > integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position: 191 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182) at 

screen.PNG

[Felix Grund (Scandio)]

Please simplify your query to troubleshoot and run it with your default Postgres tool:

SELECT ID, FINISH_DATE
FROM os_historystep
WHERE FINISH_DATE > '2010-01-01'

Only if this works without PQ, the firsts steps in PQ should be made. This is more a Postgres issue than a PQ issue.

[artafon]

In your case query is done.

But I need to set period on confluence page, because I write reports, business users using it 

I'll try to resolve this problem on postgre side

Thx

[artafon]

In Postgre, it works so:

SELECT ID, FINISH_DATE
FROM os_historystep
WHERE finish_date > CAST(:test AS date);

where test - is parameter

[Felix Grund (Scandio)]

So does this not work the same for your query?

[artafon]

it just still works only for postgre)