SQL SECOND_MICROSECOND

What is SQL SECOND_MICROSECOND?

SECOND_MICROSECOND is a MySQL composite time-unit keyword used in INTERVAL expressions to represent seconds with microsecond precision.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SECOND_MICROSECOND:

SQL SECOND_MICROSECOND Full Explanation

In MySQL, SECOND_MICROSECOND is one of several composite interval units that combine two temporal parts—in this case seconds and microseconds. It is used in conjunction with the INTERVAL keyword inside functions such as DATE_ADD, DATE_SUB, ADDDATE, SUBDATE, TIMESTAMPADD, and TIMESTAMPDIFF. The interval value may be supplied as either a numeric literal or a string literal. When numeric, the portion before the decimal point denotes seconds while the fractional part denotes microseconds. When a string literal is used, it must follow the format 'SSSSSS.MICROS', where SSSSSS is a zero-padded second value (0-59) and MICROS is a six-digit microsecond value (000000-999999).MySQL stores microseconds in the range 0-999999. If a supplied value contains more than six fractional digits, it is rounded. If the value is outside valid ranges a warning or error is thrown, depending on SQL mode. SECOND_MICROSECOND cannot be used alone in DDL statements; it is only meaningful inside date-time arithmetic expressions.

SQL SECOND_MICROSECOND Syntax

-- Stand-alone interval literal
INTERVAL <expr> SECOND_MICROSECOND

-- Inside date arithmetic
DATE_ADD(date_expression, INTERVAL <expr> SECOND_MICROSECOND)
DATE_SUB(date_expression, INTERVAL <expr> SECOND_MICROSECOND)
TIMESTAMPADD(SECOND_MICROSECOND, <expr>, date_expression)
TIMESTAMPDIFF(SECOND_MICROSECOND, date1, date2)

SQL SECOND_MICROSECOND Parameters

expr (DECIMAL or STRING) - Number of seconds and microseconds to add, subtract, or compare. Format examples: 1.500000, 60.000001, '01.000123'.

Example Queries Using SQL SECOND_MICROSECOND

-- Add 1.500000 seconds to a timestamp
SELECT DATE_ADD('2025-01-01 12:00:00.000000',
               INTERVAL 1.5 SECOND_MICROSECOND);

-- Subtract 750 microseconds
SELECT DATE_SUB('2025-01-01 12:00:00.000750',
               INTERVAL 0.000750 SECOND_MICROSECOND);

-- Difference between two timestamps in microseconds
SELECT TIMESTAMPDIFF(SECOND_MICROSECOND,
                     '2025-01-01 12:00:00.000000',
                     '2025-01-01 12:00:01.250000');

Expected Output Using SQL SECOND_MICROSECOND

First query returns '2025-01-01 12:00:01
500000'
Second query returns '2025-01-01 12:00:00
000000'
Third query returns 1
250000, the interval in seconds
microseconds between the two timestamps

Use Cases with SQL SECOND_MICROSECOND

Add or subtract high-precision time offsets to timestamps.
Measure elapsed time between two events down to microseconds.
Schedule jobs or log events where sub-second accuracy matters, such as financial trades or IoT sensor data.

Common Mistakes with SQL SECOND_MICROSECOND

Supplying more than six digits in the fractional part, which causes rounding.
Using SECOND_MICROSECOND with a value formatted like 'HH:MM:SS' instead of 'SSSSSS.MICROS'.
Forgetting that TIMESTAMPDIFF returns an integer; for SECOND_MICROSECOND it returns total microseconds, not a second.microsecond decimal.

First Introduced In

MySQL 4.1

Frequently Asked Questions

What numeric format does SECOND_MICROSECOND expect?

A numeric literal uses the part before the decimal for seconds and the six-digit fractional part for microseconds (e.g., 2.750000 means 2 seconds 750000 microseconds).

Can I supply the interval as a string?

Yes. Supply a string like '02.750000'. MySQL converts it to the correct internal representation.

Does MySQL round or truncate extra microsecond digits?

Values with more than six fractional digits are rounded to the nearest microsecond.

How do I find the difference between two timestamps to microsecond accuracy?

Use TIMESTAMPDIFF(SECOND_MICROSECOND, earlier, later). The result is the total number of microseconds between the two values.