# Microseconds in MariaDB

The [TIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/time), [DATETIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime), and [TIMESTAMP](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/timestamp) types, along with the temporal functions, [CAST](https://mariadb.com/docs/server/reference/sql-functions/string-functions/cast) and [dynamic columns](https://mariadb.com/docs/server/reference/sql-structure/nosql/dynamic-columns), support microseconds. The datetime precision of a column can be specified when creating the table with [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table), 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](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date), [DATE\_FORMAT](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date_format) and [FROM\_UNIXTIME](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/from_unixtime) 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](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/date), or `TIMESTAMP`), both values are compared as temporal values, not as strings.
* The [INFORMATION\_SCHEMA.COLUMNS table](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-columns-table) has a new column `DATETIME_PRECISION`
* [NOW()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/now), [CURTIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/curtime), [UTC\_TIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/utc_timestamp), [UTC\_TIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/utc_time), [CURRENT\_TIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/current_time), [CURRENT\_TIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/current_timestamp), [LOCALTIME()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/localtime) and [LOCALTIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/localtimestamp) accept datetime precision as an optional argument. For example:

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

* [TIME\_TO\_SEC()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/time_to_sec) and [UNIX\_TIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/unix_timestamp) preserve microseconds of the argument. These functions will return a [decimal](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/decimal) number if the result non-zero datetime precision and an [integer](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/int) 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](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysqlgeneral_log-table) table and the `start_time`, `query_time`, and `lock_time` fields in the [mysql.slow\_log](https://mariadb.com/docs/server/reference/system-tables/the-mysql-database-tables/mysql-slow_log-table) 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](https://mariadb.com/docs/server/reference/system-tables/information-schema/time_ms-column-in-information_schemaprocesslist) has been added to the [INFORMATION\_SCHEMA.PROCESSLIST](https://mariadb.com/docs/server/reference/system-tables/information-schema/information-schema-tables/information-schema-processlist-table) table, as well as to the output of [SHOW FULL PROCESSLIST](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/show/show-processlist).

**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](https://mariadb.com/docs/server/reference/data-types/data-type-storage-requirements)

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

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