MySQL MICROSECOND() – Extract Microsecond Values in MySQL

By /
MICROSECOND()

In this article, we will study the MySQL MICROSECOND() function. So far in the MysQL CURTIME() and MySQL NOW() articles, we saw we can display the current time and datetime values upto certain fractional seconds precision. The time returned with a fractional seconds precision value contains what we call the microsecond value. This value is anywhere from 0 to 999999.

So in the following time – ‘14:25:45.605844’, the numbers after the dot, is the microsecond value in the given time. Extracting the microseconds value can be very important as microseconds forms an important metric in physics.

Therefore, if we are working with databases and tables which store data relevant to some physics experiment that needs these microseconds values extracted, then we need a way to get these values.

Fortunately, MySQL provides us with the MICROSECOND() function. The MySQL MICROSECOND() is used to return the microsecond part of a time/datetime. The value ranges from 0 to 999999.

Syntax of MySQL MICROSECOND()

MICROSECOND(expression)Code language: SQL (Structured Query Language) (sql)

Where ‘expression’ is a time or datetime or a column name with time/datetime values.

Examples of the MySQL MICROSECOND() function

Let us begin with a basic example. Suppose we have the time value – ‘06:12:05.123456’. Let us use the MICROSECOND() function to display the microsecond part in the above time. We will use the SELECT statement and an alias called ‘Microseconds’ to make our output readable. The query is –

SELECT MICROSECOND('06:12:05.123456') AS Microseconds;Code language: SQL (Structured Query Language) (sql)

And the output is –

Microsecond Basic Example

1. Using A Datetime Value With MICROSECOND()

Let us now use the following datetime value with the MICROSECOND() function – ‘2021-03-04 06:12:05.426533’. The query is –

SELECT MICROSECOND('2021-03-04 06:12:05.426533') AS Microseconds;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL MICROSECOND Datetime

2. Using A Value With No Microsecond Part

What if our time/datetime value has no microsecond part at all? For instance, look at this datetime value – ‘2021-03-04 06:12:05’. There is no microsecond part in it. If we pass this value to the MySQL MICROSECOND() function, what do you think the output will be? Let us see this using the below query.

SELECT MICROSECOND('2021-03-04 06:12:05') AS Microseconds;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL MICROSECOND No Value

3. Value Where Microsecond Value is not upto 6 places

While specifying the ‘fsp’ parameter in the CURTIME() and NOW() functions, we could specify a number from 1 to 6 so as to get the seconds precision upto that number. Let us see a couple of examples where the microseconds part in the time/datetime value is between 1 to 6. Consider the below query.

SELECT MICROSECOND('2021-03-04 06:12:05.00001') AS Microseconds; 
SELECT MICROSECOND('2021-03-04 06:12:05.21') AS Microseconds;Code language: SQL (Structured Query Language) (sql)

And the output is –

Microsecond Less Than 6

Note that the first query resulted ‘10’ instead of ‘00001’ because of a couple of reasons –

  1. MySQL MICROSECOND() automatically ignores the preceding zeros in the output to make it readable.
  2. The microsecond values are expressed in terms of fractional second precisions. So, if you were to compare 0.1 with 0.001, that would mean you are comparing 0.100 with 0.001. MICROSECOND() adds those successive zeros in the output for us. This is also done kind of owing to point 1 because if the successive zeros are not added then we will get 1 as the answer which is wrong.

Let us look at one more example –

SELECT MICROSECOND('2021-03-04 06:12:05.000021') AS Microseconds;Code language: SQL (Structured Query Language) (sql)

And the output is –

Microsecond Small Value

Notice, the preceding zeros were removed in the output and since the value was already six digits long, there was no need to add successive zeros.

4. CURTIME() with MICROSECOND()

We can also pass functions like CURTIME() with the MICROSECOND() function. Let us see an example where we pass a CURTIME() function with the fsp parameter specified to the MICROSECOND() function. The query is –

SELECT CURTIME(5) AS Time, MICROSECOND(CURTIME(5)) AS Microseconds;Code language: SQL (Structured Query Language) (sql)

And the output is –

Microsecond Curtime

Conclusion

You probably will not find yourself using the MySQL MICROSECOND() function quite often for daily projects while working with databases. However, displaying the microsecond part in the time/datetime value together has many use cases across various applications and domains.

References