How to display query run time in minutes and seconds instead of milliseconds in vsql?

BillU

I use vsql on Mac to access Vertica and currently use the /timing setting in my .vsqlrc file to display the time it took to run a query. However, the time is displayed in milliseconds, which is hard to interpret quickly when query runtime is longer. How can the query run time be displayed in minutes and seconds?

 

Help would be much appreciated!

Adrian_Oprea_1

 I think you should use the query_requets table for this information.

 

Note:

\timing - actually is only going to show the value of the query execution + output formating ! so is not accurate if may say, formating can take some time depending on your client .

 

You should use the query_requests table for actaul query execution times.

 

See example here :

 

- make sure you use labels (easy to identify)

dbadmin=> \timing
Timing is on.
dbadmin=> select /*+ LABEL (MyUniqueLabel) */ 5 from dual;
 ?column?
----------
        5
(1 row)
Time: First fetch (1 row): 46.925 ms. All rows formatted: 46.996 ms

Query the query_requests internal table

- you need ot use the label and the session_id (if you run it sequentialy to the first query), else remove the session_id

dbadmin=> select sum(request_duration_ms) from v_monitor.query_requests where session_id in (select current_session()) and request_label='MyUniqueLabel';
 sum
-----
  34
(1 row)

- see that is actaully a differeance in time.

 

Or if you want to see the time in min as you initialy wanted:

select (sum(request_duration_ms)/1000)/60 from v_monitor.query_requests where session_id in (select current_session()) and request_label='MyUniqueLabel';