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

anhphuong

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!

Jim_Knicely

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