The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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

  • Jim_KnicelyJim_Knicely Administrator

    Hi,

    The \TIMING meta-command output is not configurable. Run times are always reported in milliseconds. But I get what you are saying.

    Don't forget that vsql can be used as a calculator so you can quickly convert milliseconds to minutes/seconds.

    Example:

    dbadmin=> \timing on
    Timing is on.
    
    dbadmin=> SELECT COUNT(*) FROM big_fact CROSS JOIN big_fact b CROSS JOIN big_fact c;
       COUNT
    -------------------
     40000000000000000
    (1 row)
    
    Time: First fetch (1 row): 2234283.935 ms. All rows formatted: 2234283.935 ms
    
    dbadmin=> SELECT CAST(2234283.935/1000.0::VARCHAR || ' sec' AS INTERVAL) "Run Time";
        Run Time
    -----------------
     00:37:14.283935
    (1 row)
    
    Time: First fetch (1 row): 12.165 ms. All rows formatted: 12.214 ms
    

    That run time is now more read able as Hours:Minutes:Seconds.

    You can even create your own simple conversion/display function:

    dbadmin=> CREATE OR REPLACE FUNCTION readable_mills(x NUMERIC) RETURN INTERVAL
    dbadmin-> AS
    dbadmin-> BEGIN
    dbadmin->   RETURN CAST(x/1000.0::VARCHAR || ' sec' AS INTERVAL);
    dbadmin-> END;
    CREATE FUNCTION
    Time: First fetch (0 rows): 26.697 ms. All rows formatted: 26.713 ms
    
    dbadmin=> SELECT readable_mills(2234283.935);
      readable_mills
    -----------------
     00:37:14.283935
    (1 row)
    
    Time: First fetch (1 row): 13.572 ms. All rows formatted: 13.633 ms
    

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.