MySQL - MICROSECOND() Function


The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL MICROSECOND() function is used to retrieve and return the microseconds from the given date time or time expression. This returns a numerical value ranging from 0 to 999999.

Syntax

Following is the syntax of the above function –

MICROSECOND(time);

Where, time is the time expression from which you need to extract the microsecond.

Example 1

Following example demonstrates the usage of the MICROSECOND() function –

mysql> SELECT MICROSECOND('00:00:00 09:40:45.2300');
+---------------------------------------+
| MICROSECOND('00:00:00 09:40:45.2300') |
+---------------------------------------+
| 230000                                |
+---------------------------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT MICROSECOND('00 12:38:48');
+----------------------------+
| MICROSECOND('00 12:38:48') |
+----------------------------+
| 0                          |
+----------------------------+
1 row in set (0.00 sec)

Example 3

We can also pass the date-time expression as an argument to this function –

mysql> SELECT MICROSECOND('2015-09-05 22:40:45.2300');
+-----------------------------------------+
| MICROSECOND('2015-09-05 22:40:45.2300') |
+-----------------------------------------+
| 230000                                  |
+-----------------------------------------+
1 row in set (0.00 sec)

Example 4

In the following example we are retrieving the microsecond value from the current timestamp —

mysql> SELECT MICROSECOND(CURRENT_TIMESTAMP);
+--------------------------------+
| MICROSECOND(CURRENT_TIMESTAMP) |
+--------------------------------+
| 0                              |
+--------------------------------+
1 row in set (0.00 sec)

Example 5

Suppose we have created a table named ProductSales with 5 records in it using the following queries –

mysql> CREATE TABLE ProductSales(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp VARCHAR(255),
	Price INT,
	Location VARCHAR(255)
);
insert into ProductSales values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45.6547552'), 7000, 'Hyderabad');
insert into ProductSales values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12.6321561'), 2000, 'Vishakhapatnam');
insert into ProductSales values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37.26112'), 3000, 'Vijayawada');
insert into ProductSales values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45.261123'), 9000, 'Chennai');
insert into ProductSales values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27.21223'), 6000, 'Goa');

Following query retrieves the microsecond values from the DispatchTimeStamp column —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, MICROSECOND(DispatchTimeStamp) FROM ProductSales;
+-------------+--------------+----------------------------+-------+--------------------------------+
| ProductName | CustomerName | DispatchTimeStamp          | Price | MICROSECOND(DispatchTimeStamp) |
+-------------+--------------+----------------------------+-------+--------------------------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45.654755 | 7000  | 654755                         |
| Earphones   | Roja         | 2019-06-26 14:13:12.632156 | 2000  | 632156                         |
| Mouse       | Puja         | 2019-12-07 07:50:37.26112  | 3000  | 261120                         |
| Mobile      | Vanaja       | 2018-03-21 16:00:45.261123 | 9000  | 261123                         |
| Headset     | Jalaja       | 2018-12-30 10:49:27.21223  | 6000  | 212230                         |
+-------------+--------------+----------------------------+-------+--------------------------------+
5 rows in set (0.00 sec)

Example 6

Suppose we have created a table named Subscriber with 5 records in it using the following queries –

mysql> CREATE TABLE Subscriber (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionTimeStamp VARCHAR(255)
);
insert into Subscriber values('Raja', 'Premium', TimeStamp('2020-10-21 20:53:49.111233'));
insert into Subscriber values('Roja', 'Basic', TimeStamp('2020-11-26 10:13:19.271653'));
insert into Subscriber values('Puja', 'Moderate', TimeStamp('2021-03-07 05:43:20.98292'));
insert into Subscriber values('Vanaja', 'Basic', TimeStamp('2021-02-21 16:36:39.022323'));
insert into Subscriber values('Jalaja', 'Premium', TimeStamp('2021-01-30 12:45:45.45262'));

Following query retrieves and displays the microsecond value from the subscription time for all the records —

mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp, MICROSECOND(SubscriptionTimeStamp) FROM Subscriber;
+----------------+-------------+----------------------------+------------------------------------+
| SubscriberName | PackageName | SubscriptionTimeStamp      | MICROSECOND(SubscriptionTimeStamp) |
+----------------+-------------+----------------------------+------------------------------------+
| Raja           | Premium     | 2020-10-21 20:53:49.111233 | 111233                             |
| Roja           | Basic       | 2020-11-26 10:13:19.271653 | 271653                             |
| Puja           | Moderate    | 2021-03-07 05:43:20.98292  | 982920                             |
| Vanaja         | Basic       | 2021-02-21 16:36:39.022323 | 22323                              |
| Jalaja         | Premium     | 2021-01-30 12:45:45.45262  | 452620                             |
+----------------+-------------+----------------------------+------------------------------------+
5 rows in set (0.00 sec)
mysql-date-time-functions.htm
Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements