How the MICROSECOND() function works in Mariadb?

The MICROSECOND() function is a useful tool for extracting the microsecond part of a time or datetime value.

Posted on

The MICROSECOND() function is a useful tool for extracting the microsecond part of a time or datetime value. It can be used for various purposes, such as measuring the elapsed time, generating unique identifiers, and performing time calculations.

Syntax

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

MICROSECOND(time)

The function takes one argument:

  • time: A time or datetime value that represents the input to extract the microsecond part from. It can be any valid expression that returns a time or datetime value, such as a column name, a literal, or a function.

The function returns an integer value that represents the microsecond part of the input time or datetime value, ranging from 0 to 999999.

Examples

In this section, we will show some examples of how to use the MICROSECOND() function in different scenarios.

Example 1: Extracting the microsecond part of a literal time value

Suppose you want to extract the microsecond part of a literal time value, such as ‘12:34:56.789012’. You can use the MICROSECOND() function to do so. For example, you can execute the following statement:

SELECT MICROSECOND('12:34:56.789012');

This will return the microsecond part of the time value, which is 789012. For example, the result might look like this:

+--------------------------------+
| MICROSECOND('12:34:56.789012') |
+--------------------------------+
|                         789012 |
+--------------------------------+

Note that the microsecond part is the fractional part of the second value, which can have up to six digits. For example, the microsecond part of the time value ‘12:34:56.7’ is 700000, not 7.

Example 2: Extracting the microsecond part of a column value

Suppose you have a table called events that stores the information of various events, such as their name, start_time, and end_time. The start_time and end_time columns are datetime values that represent the start and end time of the event. You want to extract the microsecond part of the start_time and end_time of each event, so that you can measure the elapsed time, generate unique identifiers, and perform time calculations. You can use the MICROSECOND() function to do so. For example, you can execute the following statement:

SELECT name, start_time, end_time,
MICROSECOND(start_time) AS start_microsecond,
MICROSECOND(end_time) AS end_microsecond FROM events;

This will return the name, start_time, end_time, and the microsecond part of the start_time and end_time of each event, or an empty result set if the table is empty. For example, the result might look like this:

+----------+----------------------------+----------------------------+-------------------+-----------------+
| name     | start_time                 | end_time                   | start_microsecond | end_microsecond |
+----------+----------------------------+----------------------------+-------------------+-----------------+
| Event A  | 2024-01-01 10:00:00.123456 | 2024-01-01 11:00:00.654321 | 123456            | 654321          |
| Event B  | 2024-01-02 12:00:00.000001 | 2024-01-02 13:00:00.999999 | 1                 | 999999          |
| Event C  | 2024-01-03 14:00:00.500000 | 2024-01-03 15:00:00.500000 | 500000            | 500000          |
+----------+----------------------------+----------------------------+-------------------+-----------------+

Note that the microsecond part is the fractional part of the second value, which can have up to six digits. For example, the microsecond part of the start_time of Event B is 1, not 000001.

There are some other functions that are related to the MICROSECOND() function and can be used to perform other time operations in Mariadb. Here are some of them:

  • SECOND(): This function returns the second part of a time or datetime value, ranging from 0 to 59.
  • MINUTE(): This function returns the minute part of a time or datetime value, ranging from 0 to 59.
  • HOUR(): This function returns the hour part of a time or datetime value, ranging from 0 to 23.
  • DAY(): This function returns the day part of a date or datetime value, ranging from 1 to 31.
  • MONTH(): This function returns the month part of a date or datetime value, ranging from 1 to 12.
  • YEAR(): This function returns the year part of a date or datetime value, ranging from 1000 to 9999.

Conclusion

The MICROSECOND() function is a powerful and flexible function that can help you extract the microsecond part of a time or datetime value. It can be used for various purposes, such as measuring the elapsed time, generating unique identifiers, and performing time calculations. You can also use some other related functions to extract other time parts, such as second, minute, hour, day, month, or year. By using these functions, you can achieve a better analysis and understanding of your time data.