Exploring Timestamps and Millisecond Precision with MariaDB

Understanding Timestamps

In MariaDB, the timestamp data type can be defined with an optional fractional seconds part. You can specify fractional seconds support using the syntax timestamp(N), where N represents the number of digits in the fractional seconds part, ranging from 1 to 6. This is needed when you're dealing with time-sensitive applications and is easy to do with MariaDB and now(N).

Example:

The Table Schema

CREATE TABLE `_timetest` (
  `n_ct` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `n` timestamp NULL DEFAULT NULL,
  `n_1` timestamp(1) NULL DEFAULT NULL,
  `n_2` timestamp(2) NULL DEFAULT NULL,
  `n_3` timestamp(3) NULL DEFAULT NULL,
  `n_4` timestamp(4) NULL DEFAULT NULL,
  `n_5` timestamp(5) NULL DEFAULT NULL,
  `n_6` timestamp(6) NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      

Example Input

While you can insert timestamps manually, MariaDB provides the now() function for inserting the current timestamp, which can also be used with fractional seconds.

INSERT INTO _timetest (n, n_1, n_2, n_3, n_4, n_5, n_6)
VALUES (
  now(),
  now(1),
  now(2),
  now(3),
  now(4),
  now(5),
  now(6)
);

Example Output

The SELECT statement:

  SELECT * FROM _timetest;  

The output

+---------------------+---------------------+---------------------+---------------------+-------------------------+--------------------------+---------------------------+----------------------------+
| n_ct                | n                   | n_1                 | n_2                 | n_3                     | n_4                      | n_5                       | n_6                        |
+---------------------+---------------------+---------------------+---------------------+-------------------------+--------------------------+---------------------------+----------------------------+
| 2023-09-10 12:34:56 | 2023-09-10 12:34:56 | 2023-09-10 12:34:56 | 2023-09-10 12:34:56 | 2023-09-10 12:34:56.123 | 2023-09-10 12:34:56.1234 | 2023-09-10 12:34:56.12345 | 2023-09-10 12:34:56.123456 |
+---------------------+---------------------+---------------------+---------------------+-------------------------+--------------------------+---------------------------+----------------------------+