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

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! :)

Comments

  •  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';

     

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.