N1QL all queries executed current_timestamp -5 minutes

SQL++
1

Hi All,
Whenever I need metrics of queries being executed in last 5 min I supply requestTime manually as in below query.
select requestTime,statement,serviceTime,node from system:completed_requests where users=‘TEST’ and(requestTime >= “2019-04-08 21:00:00.000000000 -0700 MST” and **
requestTime <= “2019-04-08 21:45:00.000000000 -0700 MST”) ;

I am trying to create something like

**select requestTime,statement,serviceTime,node from system:completed_requests where users=‘test’ and DATE_DIFF_STR(clock_str(),requestTime,‘second’) < 300 **

But above query returns null results always.

Also ,select DATE_FORMAT_STR(requestTime, ‘1111-11-11T00:00:00+00:00’) from system:completed_requests limit 1 results in null, if i want to transform requestTime format and use in mainquery

sample value “requestTime”: "2019-04-09 12:34:09.473408357 -0700 MST"

2 
SELECT c.requestTime, c.statement, c.serviceTime, c.node
FROM system:completed_requests  AS c
WHERE c.users = "TEST"
      AND requestTime BETWEEN  DATE_ADD_STR(NOW_STR(),-5, "hour") AND NOW_STR();

“2019-04-09 12:34:09.473408357 -0700 MST” Is not ISO-8601 format. You already have timezone and -0700 and have additional MST. strip the MST. At present there is no easy way to convert requestTime into givenformat beucase it needs in ISO-8601 format (strip also will not help because there is space before timezone).

The following link has all the information on datetime functions.
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/datefun.html

1 Like
3

@vsr1 I tried the query seems like it does not gives correct result.
I ran below on my DB and it gave zero results.

SELECT c.requestTime, c.statement, c.serviceTime, c.node
FROM system:completed_requests AS c
WHERE requestTime BETWEEN DATE_ADD_STR(NOW_STR(),-5, “hour”) AND NOW_STR() order by c.requestTime;

my profiling status on server is as below … So i am eventually logging everything
completed-limit":4000,“completed-threshold”:0,
Can you please check if timestamp format requestTime causes this
“requestTime”: “2019-04-09 12:34:09.473408357 -0700 MST”

4

I mentioned earlier requestTime is ISO-8601 format first convert that to ISO-8601 format and then compare.

SELECT c.requestTime, c.statement, c.serviceTime, c.node 
FROM system:completed_requests  AS c 
WHERE  substr(c.requestTime,0,10) ||"T" || substr(c.requestTime,11,8)  BETWEEN  DATE_ADD_STR(NOW_STR(),-5, "hour") AND NOW_STR();
1 Like