How the FORMAT_PICO_TIME() function works in Mariadb?

The FORMAT_PICO_TIME() function is a string function that returns a human-readable time value and unit indicator for a given time in picoseconds. It can be used to display the execution time of queries or functions in a readable format.

Syntax

The syntax of the FORMAT_PICO_TIME() function is as follows:

FORMAT_PICO_TIME(time_val)

The function takes one argument:

time_val is the time in picoseconds to be formatted.

The function returns a string value that consists of a numeric value and a unit indicator. The unit indicator depends on the length of the argument, and can be one of the following:

ps - picoseconds
ns - nanoseconds
us - microseconds
ms - milliseconds
s - seconds
m - minutes
h - hours
d - days

The numeric value is rounded to two decimal places, with a minimum of three significant digits. If the argument is NULL, the function returns NULL.

Examples

Example 1: Using FORMAT_PICO_TIME() function with literal values

The following example shows how to use the FORMAT_PICO_TIME() function with literal values:

SELECT FORMAT_PICO_TIME(1234567890123456789);
SELECT FORMAT_PICO_TIME(1234567890123456);
SELECT FORMAT_PICO_TIME(1234567890123);
SELECT FORMAT_PICO_TIME(1234567890);
SELECT FORMAT_PICO_TIME(1234567);
SELECT FORMAT_PICO_TIME(1234);
SELECT FORMAT_PICO_TIME(1);

The function returns the formatted time values and unit indicators for each value.

+---------------------------------------+
| FORMAT_PICO_TIME(1234567890123456789) |
+---------------------------------------+
| 14.29 d                               |
+---------------------------------------+

+------------------------------------+
| FORMAT_PICO_TIME(1234567890123456) |
+------------------------------------+
| 20.58 min                          |
+------------------------------------+

+---------------------------------+
| FORMAT_PICO_TIME(1234567890123) |
+---------------------------------+
| 1.23 s                          |
+---------------------------------+

+------------------------------+
| FORMAT_PICO_TIME(1234567890) |
+------------------------------+
| 1.23 ms                      |
+------------------------------+

+---------------------------+
| FORMAT_PICO_TIME(1234567) |
+---------------------------+
| 1.23 us                   |
+---------------------------+

+--------------------------+
| FORMAT_PICO_TIME(1234)   |
+--------------------------+
| 1.23 ns                  |
+--------------------------+

+------------------------+
| FORMAT_PICO_TIME(1)    |
+------------------------+
| 1 ps                   |
+------------------------+

Example 2: Using FORMAT_PICO_TIME() function with NULL

The following example shows how to use the FORMAT_PICO_TIME() function with NULL:

SELECT FORMAT_PICO_TIME(NULL);

The function returns NULL, because NULL is not a valid time value.

+------------------------+
| FORMAT_PICO_TIME(NULL) |
+------------------------+
| NULL                   |
+------------------------+

Example 3: Using FORMAT_PICO_TIME() function with expressions

The following example shows how to use the FORMAT_PICO_TIME() function with expressions. Suppose we have a table called employees that has the following data:

+----+-------+--------+------------+
| id | name  | salary | hire_date  |
+----+-------+--------+------------+
|  1 | Alice |  5000  | 2020-01-01 |
|  2 | Bob   |  6000  | 2020-02-01 |
|  3 | Carol |  7000  | 2020-03-01 |
|  4 | David |  8000  | 2020-04-01 |
|  5 | Eve   |  9000  | 2020-05-01 |
+----+-------+--------+------------+

We can use the FORMAT_PICO_TIME() function to measure the execution time of a query that calculates the average salary of the employees, by using the BENCHMARK() function to repeat the query 1000 times and the TIMESTAMPDIFF() function to get the difference in picoseconds between the start and end time of the query:

SET @start = CURRENT_TIMESTAMP(6);
SELECT BENCHMARK(1000, (SELECT AVG(salary) FROM employees));
SET @end = CURRENT_TIMESTAMP(6);
SELECT FORMAT_PICO_TIME(TIMESTAMPDIFF(PICOSECOND, @start, @end)) AS execution_time;

The function returns the formatted execution time of the query.

+----------------+
| execution_time |
+----------------+
| 2.34 ms        |
+----------------+

Some of the functions that are related to the FORMAT_PICO_TIME() function are:

BENCHMARK(): This function repeats an expression a specified number of times and returns the elapsed time in microseconds. It can be used to measure the performance of a query or a function. For example, BENCHMARK(1000, (SELECT AVG(salary) FROM employees)) returns the elapsed time in microseconds for calculating the average salary of the employees 1000 times.
TIMESTAMPDIFF(): This function returns the difference between two timestamps in a specified unit. It can be used to get the duration of an event or a process. For example, TIMESTAMPDIFF(PICOSECOND, @start, @end) returns the difference in picoseconds between the start and end time of a query.
FORMAT_TIME(): This function is a stored function available with the Sys Schema. It is similar to the FORMAT_PICO_TIME() function, but it takes the time in microseconds instead of picoseconds. For example, FORMAT_TIME(1234567890) returns ‘1.23 ms’.

Conclusion

The FORMAT_PICO_TIME() function is a useful string function that can be used to display the execution time of queries or functions in a human-readable format. It takes a time in picoseconds and returns a string value that consists of a numeric value and a unit indicator. The unit indicator depends on the length of the argument, and can range from picoseconds to days. The numeric value is rounded to two decimal places, with a minimum of three significant digits. If the argument is NULL, the function returns NULL.