> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/microseconds-in-mariadb.md).

# Microseconds in MariaDB

The [TIME](/docs/server/reference/data-types/date-and-time-data-types/time.md), [DATETIME](/docs/server/reference/data-types/date-and-time-data-types/datetime.md), and [TIMESTAMP](/docs/server/reference/data-types/date-and-time-data-types/timestamp.md) types, along with the temporal functions, [CAST](/docs/server/reference/sql-functions/string-functions/cast.md) and [dynamic columns](/docs/server/reference/sql-structure/nosql/dynamic-columns.md), support microseconds. The datetime precision of a column can be specified when creating the table with [CREATE TABLE](/docs/server/server-usage/tables/create-table.md), for example:

```sql
CREATE TABLE example(
  col_microsec DATETIME(6),
  col_millisec TIME(3)
);
```

Generally, the precision can be specified for any `TIME`, `DATETIME`, or `TIMESTAMP` column, in parentheses, after the type name. The datetime precision specifies number of digits after the decimal dot and can be any integer number from 0 to 6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.

A datetime precision can be specified wherever a type name is used. For example:

* when declaring arguments of stored routines;
* when specifying a return type of a stored function;
* when declaring variables;
* in a `CAST` function.

```sql
CREATE FUNCTION example(x DATETIME(5)) RETURNS TIME(4)
BEGIN
 DECLARE y TIMESTAMP(6);
 RETURN CAST(x AS time(2));
END;
```

`%f` is used as the formatting option for microseconds in the [STR\_TO\_DATE](/docs/server/reference/sql-functions/date-time-functions/str_to_date.md), [DATE\_FORMAT](/docs/server/reference/sql-functions/date-time-functions/date_format.md) and [FROM\_UNIXTIME](/docs/server/reference/sql-functions/date-time-functions/from_unixtime.md) functions, for example:

```sql
SELECT STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f');
+-----------------------------------------------------+
| STR_TO_DATE('20200809 020917076','%Y%m%d %H%i%s%f') |
+-----------------------------------------------------+
| 2020-08-09 02:09:17.076000                          |
+-----------------------------------------------------+
```

## Additional Information

* When comparing anything to a temporal value (`DATETIME`, `TIME`, [DATE](/docs/server/reference/data-types/date-and-time-data-types/date.md), or `TIMESTAMP`), both values are compared as temporal values, not as strings.
* The [INFORMATION\_SCHEMA.COLUMNS table](/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-columns-table.md) has a new column `DATETIME_PRECISION`
* [NOW()](/docs/server/reference/sql-functions/date-time-functions/now.md), [CURTIME()](/docs/server/reference/sql-functions/date-time-functions/curtime.md), [UTC\_TIMESTAMP()](/docs/server/reference/sql-functions/date-time-functions/utc_timestamp.md), [UTC\_TIME()](/docs/server/reference/sql-functions/date-time-functions/utc_time.md), [CURRENT\_TIME()](/docs/server/reference/sql-functions/date-time-functions/current_time.md), [CURRENT\_TIMESTAMP()](/docs/server/reference/sql-functions/date-time-functions/current_timestamp.md), [LOCALTIME()](/docs/server/reference/sql-functions/date-time-functions/localtime.md) and [LOCALTIMESTAMP()](/docs/server/reference/sql-functions/date-time-functions/localtimestamp.md) accept datetime precision as an optional argument. For example:

```sql
SELECT CURTIME(4);
--> 10:11:12.3456
```

* [TIME\_TO\_SEC()](/docs/server/reference/sql-functions/date-time-functions/time_to_sec.md) and [UNIX\_TIMESTAMP()](/docs/server/reference/sql-functions/date-time-functions/unix_timestamp.md) preserve microseconds of the argument. These functions will return a [decimal](/docs/server/reference/data-types/numeric-data-types/decimal.md) number if the result non-zero datetime precision and an [integer](/docs/server/reference/data-types/numeric-data-types/int.md) otherwise (for backward compatibility).

```sql
SELECT TIME_TO_SEC('10:10:10.12345');
--> 36610.12345
```

* Current versions of this patch fix a bug in the following optimization: In certain queries with `DISTINCT` MariaDB can ignore this clause if it can prove that all result rows are unique anyway, for example, when a primary key is compared with a constant. Sometimes this optimization was applied incorrectly, though — for example, when comparing a string with a date constant. This is now fixed.
* `DATE_ADD()` and `DATE_SUB()` functions can now take a `TIME` expression as an argument (not just `DATETIME` as before).

```sql
SELECT TIME('10:10:10') + INTERVAL 100 MICROSECOND;
--> 10:10:10.000100
```

* The `event_time` field in the [mysql.general\_log](/docs/server/reference/system-tables/the-mysql-database-tables/mysqlgeneral_log-table.md) table and the `start_time`, `query_time`, and `lock_time` fields in the [mysql.slow\_log](/docs/server/reference/system-tables/the-mysql-database-tables/mysql-slow_log-table.md) table now store values with microsecond precision.
* The old syntax `TIMESTAMP(N)`, where `N` is the display width, is no longer supported.
* When a `DATETIME` value is compared to a `TIME` value, the latter is treated as a full datetime with a zero date part, similar to comparing `DATE` to a `DATETIME`, or to comparing `DECIMAL` numbers.\
  Earlier versions of MariaDB used to compare only the time part of both operands in such a case.
* In MariaDB, an extra column [TIME\_MS](/docs/server/reference/system-tables/information-schema/time_ms-column-in-information_schemaprocesslist.md) has been added to the [INFORMATION\_SCHEMA.PROCESSLIST](/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-processlist-table.md) table, as well as to the output of [SHOW FULL PROCESSLIST](/docs/server/reference/sql-statements/administrative-sql-statements/show/show-processlist.md).

**Note:** When you convert a temporal value to a value with a smaller precision, it will be truncated, not rounded. This is done to guarantee that the date part is not changed. For example:

```sql
SELECT CAST('2009-12-31 23:59:59.998877' AS DATETIME(3));
-> 2009-12-31 23:59:59.998
```

## See Also

* [Data Type Storage Requirements](/docs/server/reference/data-types/data-type-storage-requirements.md)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}