Exploring Timestamps and Millisecond Precision with MariaDB

^{Photo by Peter Yost on Unsplash}

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 |
+---------------------+---------------------+---------------------+---------------------+-------------------------+--------------------------+---------------------------+----------------------------+